These functions perform operations on timestamp values:
CURRENT_TIMESTAMP
CURRENT_DATE
DATEADD
DATEDIFF
DATEPART
DATESERIAL
When using these functions, use the control strings listed in Table 6‑9 to represent units of time. The control string used in a function must be a literal string, not an expression or a parameter.
Table 6‑9 Control strings for various units of time
Unit of time
Control string
year
yyyy
quarter
q
month
m
day
d
day of year
y
day of week
w
hour
h
minute
n
second
s
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP returns a timestamp value for the current date and time:
Timestamp CURRENT_TIMESTAMP()
Example
The following code:
SELECT CURRENT_TIMESTAMP()
returns:
2004-10-27 14:49:23.0
CURRENT_DATE
CURRENT_DATE returns a timestamp value for the current date with the time set to 00:00:00.0:
Timestamp CURRENT_DATE()
Example
The following code:
SELECT CURRENT_DATE()
returns:
2004-10-27 00:00:00.0
DATEADD
DATEADD takes three arguments: a control string, an integer delta value, and a timestamp value. It returns a timestamp that applies the delta value to the specified part of the original timestamp. The operation carries if the sum of the original field value and the delta is illegal:
Timestamp DATEADD( control Varchar, delta Integer, value Timestamp )
Example
The following code:
SELECT ORDERS.ORDERID, ORDERS.SHIPBYDATE, DATEADD('d', 14, ORDERS.SHIPBYDATE) AS ExpectedDelivery
FROM "../Data Sources/MyDatabase/ORDERS.SMA" ORDERS
returns:
1645,1995-05-22 00:00:00.0,1995-06-05 00:00:00.0
1340,1995-06-03 00:00:00.0,1995-06-17 00:00:00.0
1810,1995-04-12 00:00:00.0,1995-04-26 00:00:00.0
…
DATEDIFF
DATEDIFF takes three arguments: a control string, a start timestamp, and an end timestamp. It returns the integer delta between the part of the two timestamps specified by the control string. Components smaller than the control string are ignored. Components larger than the control string contribute to the result:
Integer DATEDIFF( control Varchar, start Timestamp, end Timestamp )
Examples
The following code:
SELECT ORDERS.ORDERID, ORDERS.SHIPBYDATE, ORDERS.FORECASTSHIPDATE, DATEDIFF('d', ORDERS.SHIPBYDATE, ORDERS.FORECASTSHIPDATE) AS ShipDateDifference
FROM "../Data Sources/MyDatabase/ORDERS.SMA" ORDERS
DATEDIFF('d', CAST('2005-12-31 23:59:59.0' AS TIMESTAMP), CAST('2006-01-01 00:00:00.0' AS TIMESTAMP))
returns 1. The control string d indicates that the difference is in days. The difference between December 31, 2005 and January 1, 2006 is one day. The hours, minutes, and seconds components are ignored.
The following expression:
DATEDIFF('m', CAST('2005-12-31 23:59:59.0' AS TIMESTAMP), CAST('2006-01-01 00:00:00.0' AS TIMESTAMP))
returns 1. The control string m indicates that the difference is in months. The difference between December 31, 2005 and January 1, 2006 is one month. The day, hours, minutes, and seconds components are ignored.
DATEPART
DATEPART takes two arguments: a control string and a timestamp. It returns the part of the timestamp specified by the control string:
Integer DATEPART( control Varchar, value Timestamp )
Example
The following code:
SELECT ORDERS.ORDERID, ORDERS.SHIPBYDATE
FROM "../Data Sources/MyDatabase/ORDERS.SMA" ORDERS
WHERE DATEPART('m', ORDERS.SHIPBYDATE) = 5
returns:
1645,1995-05-22 00:00:00.0
1725,1995-05-10 00:00:00.0
1125,1995-05-03 00:00:00.0
…
DATESERIAL
DATESERIAL has two forms. The first form takes three arguments: a year value, a month value, and a day value. It returns a timestamp for the date corresponding to the specified year, month, and day with the time set to 00:00:00.0:
Timestamp DATESERIAL( year Integer, month Integer, day Integer )
The second form of DATESERIAL takes six arguments: values for the year, month, day, hour, minute, and second. It returns the timestamp for the specified values:
Timestamp DATESERIAL( year Integer, month Integer, day Integer, hour Integer, minute Integer, second Integer )
Example
The following code:
SELECT ORDERS.ORDERID, ORDERS.ASKBYDATE
FROM "../Data Sources/MyDatabase/ORDERS.SMA" ORDERS
WHERE ORDERS.ASKBYDATE >= DATESERIAL(1995, 6, 15, 12, 59, 59)
CURRENT_TIMESTAMP