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

A function that takes a timestamp and returns a date.
Synopsis
DATE(timestamp)
Arguments
timestamp An expression that specifies a timestamp or other date or date and time representation.
Description
DATE takes a timestamp expression and returns a date. The return value is of data type DATE. This is functionally the same as CAST(timestamp AS DATE). It accepts timestamp values with any of the following data types: %Library.TimeStamp, %Library.Date, and %Library.Integer or %Library.Numeric (for implicit logical dates, such as +$HOROLOG). It can also accept %Library.String values that are in a format compatible with %Library.TimeStamp (a valid ODBC date).
An invalid ODBC date string is evaluated as zero, which corresponds to the date December 31, 1840. A timestamp may contain just an ODBC format date or an ODBC format date and time. Although only the date portion of the ODBC timestamp is converted, the entire string is validated. An ODBC timestamp fails validation if the date portion is incomplete, if either the date or time portion contain an out-of-range value (including leap year calculations), or if timestamp contains any invalid format characters or trailing characters.
An empty string ('') argument returns 0 (December 31, 1840). A NULL argument returns NULL.
This function can also be invoked from ObjectScript using the DATE() method call:
  WRITE $SYSTEM.SQL.DATE("2016-02-23 12:37:45")
 
$HOROLOG and $ZTIMESTAMP
$HOROLOG and $ZTIMESTAMP return character string values. When a character string is cast to a numeric type, it always returns a numeric value of zero (0). The Caché DATE data type value for 0 is December 31, 1840.
Therefore, in order to interpret $HOROLOG or $ZTIMESTAMP as the current date, you must prefix it was a plus (+) sign, which forces numeric interpretation. This is shown in the following examples:
SELECT DATE($HOROLOG),DATE($ZTIMESTAMP)  // both return 12/31/1840
SELECT DATE(+$HOROLOG),DATE(+$ZTIMESTAMP)  // both return the current date
ODBC Date Strings
The DATE function and the $SYSTEM.SQL.DATE() method can both take an ODBC date format string. They validate the input string. If it passes validation, they return the corresponding date. If it fails validation, they return 0. Validation is performed as following:
Examples
The following examples take a value of data type %Library.TimeStamp:
  SET myquery = "SELECT {fn NOW} AS NowCol,DATE({fn NOW}) AS DateCol"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
  SET myquery = "SELECT CURRENT_TIMESTAMP AS TSCol,DATE(CURRENT_TIMESTAMP) AS DateCol"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
  SET myquery = "SELECT GETDATE() AS GetDateCol,DATE(GETDATE()) AS DateCol"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
The following example takes a string value in %Library.TimeStamp format:
  SET myquery = "SELECT '1995-09-10 13:14:23' AS DateStrCol,DATE('1995-09-10 13:14:23') AS DateCol"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
The following examples take string values that represent dates in Caché logical format. In order to properly convert these values to %Library.Date data type, the value must be prefixed with a plus sign (+) to force numeric evaluation:
  SET myquery = "SELECT $H AS HoroCol,DATE(+$H) AS DateCol"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
  SET myquery = "SELECT $ZTIMESTAMP AS TSCol,DATE(+$ZTIMESTAMP) AS DateCol"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
See Also