DATE (SQL)

A function that takes a timestamp and returns a date.

Synopsis

DATE(timestamp)

Description

DATE(timestamp) takes a timestamp expression and returns a date of data type DATE. This function is equivalent to CAST(timestamp as DATE).
This statement converts a timestamp string to a date. The time portion of the timestamp is validated but not returned.
```
SELECT DATE('2000-01-01 00:00:00') AS StringToDate -- Display Mode: 01/01/2000
```
Example: Convert Timestamps of Varying Types to Dates

Arguments

timestamp

An expression that specifies a timestamp, date, or date and time representation. Specify timestamp as one of these data type classes:

%Library.TimeStampOpens in a new tab
%Library.PosixTimeOpens in a new tab
%Library.DateOpens in a new tab
%Library.IntegerOpens in a new tab
%Library.NumericOpens in a new tab values in implicit logical timestamp format, such as +$HOROLOG. See Numeric Timestamps.
%Library.StringOpens in a new tab values compatible with %Library.TimeStamp. See String Timestamps.

Numeric Timestamps

The $HOROLOG and $ZTIMESTAMP special variables specify the current date as a numeric character string. DATE casts such strings to 0, which represents the date December 31, 1840.

SELECT
  DATE($HOROLOG),   -- Returns 0 (12/31/1840)
  DATE($ZTIMESTAMP) -- Returns 0 (12/31/1840)

To interpret $HOROLOG or $ZTIMESTAMP as the current date, you must force numeric interpretation by prefixing the date with a plus (+) sign.

SELECT
  DATE(+$HOROLOG),   -- Returns the current date
  DATE(+$ZTIMESTAMP) -- Returns the current date

String Timestamps

String timestamps converted to the DATE format, must be compatible with the %Library.TimeStampOpens in a new tab data type. The data type stores strings in the ODBC date format, and the DATE function validates input strings against this format. If the string passes validation, DATE returns the corresponding date. If it fails validation, DATE returns 0, which corresponds to the date December 31, 1840. An empty string ('') also returns 0. A NULL argument returns NULL.

DATE performs these validation checks:

The string corresponds to ODBC format, where fff is fractional seconds:
```
yyyy-mm-dd hh:mm:ss.fff
```
The string contains at least a full date: yyyy-mm-dd. The time portion is optional. DATE validates any specified times but does not return them. Any part of the time can be included. For example: yyyy-mm-dd hh:
The string does not contain any invalid format characters or trailing characters. Leading zeros can be omitted or included.
Each numeric element of the string, including the optional time portion, contains a valid value given the range for that element. For example:
- Month values are from 1 to 12.
- Day values do not exceed the number of days for the specified month (includes leap year days).
- Dates are within the range of 0001-01-01 to 9999-12-31.

Examples

Convert Timestamps of Varying Formats to Dates

These statements convert timestamps of data type %Library.TimeStampOpens in a new tab to the DATE data type.

SELECT
  {fn NOW} AS NowCol,
  DATE({fn NOW}) AS DateCol

SELECT
  CURRENT_TIMESTAMP AS TSCol,
  DATE(CURRENT_TIMESTAMP) AS DateCol

SELECT
  GETDATE() AS GetDateCol,
  DATE(GETDATE()) AS DateCol

These statements convert strings written in the %Library.TimeStampOpens in a new tab format to DATE.

SELECT
  '2022-05-22 13:14:23' AS DateStrCol,
  DATE('2022-05-22 13:14:23') AS DateCol

This statement converts a %Library.PosixTimeOpens in a new tab timestamp to DATE.

SELECT
  TO_POSIXTIME('2022-05-22','YYYY-MM-DD') AS PosixCol,
  DATE(TO_POSIXTIME('2022-05-22','YYYY-MM-DD')) AS DateCol

These statements convert string values that represent dates in the InterSystems IRIS® logical format to DATE. To convert these values properly, you must force numeric evaluation by prefixing the strings with a plus sign (+).

SELECT
  $H AS HoroCol,
  DATE(+$H) AS DateCol

SELECT 
  $ZTIMESTAMP AS TSCol,
  DATE(+$ZTIMESTAMP) AS DateCol

Alternatives

To perform equivalent timestamp-to-date conversions in ObjectScript using code, use the DATE()Opens in a new tab method:

  WRITE $SYSTEM.SQL.Functions.DATE("2018-02-23 12:37:45")