Caché SQL Reference
CURRENT_DATE
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A date/time function that returns the current local date.
Synopsis
CURRENT_DATE
Description
CURRENT_DATE takes no arguments and returns the date as type DATE. Argument parentheses are not permitted. CURRENT_DATE returns the current local date for this timezone; it adjusts for local time variants, such as Daylight Saving Time.
CURDATE in Logical mode returns the current local date in $HOROLOG format; for example, 62970. CURDATE in Display mode returns the current local date in the default format for the locale. For example, in an American locale 05/28/2013, in a European locale 28/05/2013, in a Russian locale 28.05.2013.
To specify a different date format, use the TO_DATE function. To change the default date format, use the SET OPTION command with the DATE_FORMAT, YEAR_OPTION, or DATE_SEPARATOR options.
To return just the current date, use CURRENT_DATE or CURDATE. These functions return their values in DATE data type. The CURRENT_TIMESTAMP, GETDATE and NOW functions can also be used to return the current date and time as a TIMESTAMP data type.
Note that all Caché SQL time and date functions except GETUTCDATE are specific to the local time zone setting. To get a current timestamp that is universal (independent of time zone) you can use GETUTCDATE or the ObjectScript $ZTIMESTAMP special variable.
These data types perform differently when using embedded SQL. The DATE data type stores values as integers in $HOROLOG format; when displayed in SQL they are converted to date display format; when returned from embedded SQL they are returned as integers. A TIMESTAMP data type stores and displays its value in the same format. You can use the CONVERT function to change the datatype of dates and times.
CURRENT_DATE can be used as a default specification keyword in CREATE TABLE or ALTER TABLE.
Examples
The following example returns the current date, converted to Display mode:
SELECT CURRENT_DATE AS Today
 
The following Embedded SQL example returns the current date as stored. Because this date is stored in $HOROLOG format, it is returned as an integer:
  &sql(SELECT CURRENT_DATE INTO :a)
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"Current date is: ",a }
 
The following example shows how CURRENT_DATE can be used in a WHERE clause to return records of people born in the last 1000 days:
SELECT Name,DOB,Age
FROM Sample.Person
WHERE DOB > CURRENT_DATE - 1000
 
See Also
CURDATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME GETDATE GETUTCDATE NOW