CURRENT_DATE (SQL)

Synopsis

CURRENT_DATE

Description

CURRENT_DATE takes no arguments. It returns the current local date as data 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.

CURRENT_DATE in Logical mode returns the current local date in $HOROLOG format; for example, 64701. CURRENT_DATE in Display mode returns the current local date in the default format for the locale. For example, in an American locale 02/22/2018, in a European locale 22/02/2018, in a Russian locale 22.02.2018.

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 InterSystems 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 example also returns the current date, but because this date is stored in $HOROLOG format, it is returned as an integer:

SELECT CURRENT_DATE

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