CURDATE (SQL)

Synopsis

{fn CURDATE()}
{fn CURDATE}

Description

CURDATE takes no arguments. It returns the current local date as data type DATE. Note that the argument parentheses are optional. CURDATE 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, 64701. CURDATE 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 CURDATE or CURRENT_DATE. 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 data type of dates and times.

Examples

The following examples both return the current date:

SELECT {fn CURDATE()} AS Today

SELECT {fn CURDATE} AS Today

The following example returns the current date. Because this date is stored in $HOROLOG format, it is returned as an integer:

SELECT {fn CURDATE()} AS CurrentDate

The following example shows how CURDATE can be used in a SELECT statement to return all records that have a shipment date that is the same or later than today's date:

SELECT * FROM Orders 
     WHERE ShipDate >= {fn CURDATE()}

See Also

• SQL functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURTIME, GETDATE, GETUTCDATE, NOW

• ObjectScript function: $ZDATE