Databases Reference
In-Depth Information
Name
Layout
Example
ISO
yyyy-mm-dd
2010-09-25
09/25/2010
25.09.2010
2010-09-25
USA
EUR
JIS
mm/dd/yyyy
dd.mm.yyyy
yyyy-mm-dd
Figure 14.14 Date Formats for Date Literals.
We used the USA format in parts of the topic, so we will
assume that the default date format in our sample DBMS is the
same.
Different DBMSs use different syntax for date arithmetic. SQL
Server would use something like this:
AND DATEADD(DAY, -1, pol.eff_end_dt)
>
'07/15/2010'
where DAY is the granularity (which can also be abbreviated as
DD or D), while DB2 might use:
AND (pol.eff_end_dt - 1 DAY)
>
'07/15/2010'
with the reserved word DAY indicating the granularity. We will
use the T-SQL format for our examples, and will assume our
clock tick granularity is one month, to keep it in synch with
the examples used in the topic. However, in real-world
databases, the granularity would more likely be a day, a second
or a microsecond. This fCTD translation could be built into a
reusable database function as part of the framework based on
metadata.
T
1
[starts] P
1
This is a pair of relationships, one the inverse of the other. In
the non-superscripted relationship, the first time period is the
point in time, i.e. the single clock-tick time period.
Figure 14.15
shows this relationship, and its place in our taxonomy. The two
dashed lines in the illustration graphically represent T
1
and P
1
,
with T
1
being the upper dashed line.
The predicate for this relationship, as it holds between a
period of time expressed as a pair of dates using the closed-open
convention, and a point in time, is:
(T
1
ΒΌ
eff_beg_dt)
It says that T
1
starts at P
1
.
Consider the following request for information: which
policies begin on the same date as the 2009 Diabetes Manage-
ment Wellness Program?
