Home|Management Portal|Index
Caché SQL Reference
Date and Time Constructs
« »
   
Server:docs.intersystems.com
Instance:CACHE20102
User:UnknownUser
 
-
Go to:
Search:    

Validates and converts an ODBC date, time, or timestamp.
Synopsis
{d 'yyyy-mm-dd'}

{t 'hh:mm:ss[.fff]'}

{ts 'yyyy-mm-dd [hh:mm:ss.fff]'}
{ts 'mm/dd/yyyy [hh:mm:ss.fff]'}
{ts 'nnnnn'}
Description
These constructs take a string in ODBC date, time, or timestamp format and convert it to the corresponding Caché date, time, or timestamp format. They perform data typing and value and range checking.
{d 'string'}
The {d 'string'} date construct validates a date in ODBC format. If the date is valid, it stores it (logical mode) in Caché $HOROLOG date format as an integer count value from 1840-12-31. Caché does not append a default time value.
If you supply:
{t 'string'}
The {t 'string'} time construct validates a time in ODBC format. If the time is valid, it stores it (logical mode) in Caché $HOROLOG time format as an integer count of seconds from midnight, with the specified fractional seconds. Caché Display mode and ODBC mode do not display the fractional seconds; the fractional seconds are truncated from these display formats.
If you supply:
{ts 'string'}
The {ts 'string'} timestamp construct validates a date/time and returns it in ODBC timestamp format; specified fractional seconds are always preserved and displayed. The {ts 'string'} timestamp construct also validates a date and returns it in ODBC timestamp format with a suppled time value of 00:00:00.
If you supply:
See the $HOROLOG special variable in the Caché ObjectScript Reference for further information.
Examples
The following Dynamic SQL example validates dates supplied in ODBC format (with or without leading zeros) and stores them as the equivalent $HOROLOG value 59594. This example displays %SelectMode 0 (logical) values:
  SET myquery = 2
  SET myquery(1) = "SELECT {d '2004-02-29'} AS date1,"
  SET myquery(2) = "{d '2004-2-29'} AS date2"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=0
  SET tStatus = tStatement.%Prepare(.myquery)
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
The following Dynamic SQL example validates times supplied in ODBC format (with or without leading zeros) and stores them as the equivalent $HOROLOG value 43469. This example displays %SelectMode 0 (logical) values:
  SET myquery = 3
  SET myquery(1) = "SELECT {t '12:04:29'} AS time1,"
  SET myquery(2) = "{t '12:4:29'} AS time2,"
  SET myquery(3) = "{t '12:04:29.00000'} AS time3"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=0
  SET tStatus = tStatement.%Prepare(.myquery)
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
The following Dynamic SQL example validates times supplied in ODBC format with fractional seconds, and stores them as the equivalent $HOROLOG value 43469 with the fractional seconds appended. Trailing zeros are truncated. This example displays %SelectMode 0 (logical) values:
  SET myquery = 3
  SET myquery(1) = "SELECT {t '12:04:29.987'} AS time1,"
  SET myquery(2) = "{t '12:4:29.987'} AS time2,"
  SET myquery(3) = "{t '12:04:29.987000'} AS time3"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=0
  SET tStatus = tStatement.%Prepare(.myquery)
  SET rset = tStatement.%Execute()
  DO rset.%Display()
 
The following Dynamic SQL example validates time and date values in several formats and stores them as the equivalent ODBC timestamp. A time value of 00:00:00 is supplied when necessary. This example displays %SelectMode 0 (logical) values:
  SET myquery = 6
  SET myquery(1) = "SELECT {ts '2011-02-14 01:43:38'} AS ts1,"
  SET myquery(2) = "{ts '2011-02-14'} AS ts2,"
  SET myquery(3) = "{ts '02/14/2011 01:43:38.999'} AS ts3,"
  SET myquery(4) = "{ts '2/14/2011 01:43:38'} AS ts4,"
  SET myquery(5) = "{ts '02/14/2011'} AS ts5,"
  SET myquery(6) = "{ts '62136'} AS ts6"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=0
  SET tStatus = tStatement.%Prepare(.myquery)
  SET rset = tStatement.%Execute()
  WRITE rset.ts1,!
  WRITE rset.ts2,!
  WRITE rset.ts3,!
  WRITE rset.ts4,!
  WRITE rset.ts5,!
  WRITE rset.ts6