Date and Time Constructs

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:

An invalid date (such as a date not in ODBC format or the date 02-29 in a non-leap year): Caché generates an SQLCODE -146 error: “yyyy-mm-dd' is an invalid ODBC/JDBC Date value”.
A date prior to 1840-12-31: Caché generates an SQLCODE -146 error.
An ODBC timestamp value: Caché validates both the date and time portions of the timestamp. If both are valid, it converts the date portion only. If either date or time are invalid, the system generates an SQLCODE -146 error.

{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:

An invalid time (such as a time not in ODBC format or a time with hour >23): Caché generates an SQLCODE -147 error: “hh:mi:ss.fff' is an invalid ODBC/JDBC Time value”.
An ODBC timestamp value: Caché generates an SQLCODE -147 error.

{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:

A valid timestamp in ODBC format: Caché stores the supplied value unchanged This is because Caché timestamp format is the same as ODBC timestamp format.
A valid timestamp using the locale default date and time formats (for example, 2/29/2004 12:23:46.77): Caché stores and displays the supplied value in ODBC format.
An invalid timestamp (such as a timestamp with the date portion specifying 02-29 in a non-leap year, or with the time portion specifying hour >23): Caché returns the string “error” as the value.
A timestamp specifying a date prior to 1840-12-31: Caché returns the string “error” as the value.
A valid date (in ODBC or locale format) with no time value: Caché appends a time value of 00:00:00, then stores the resulting timestamp in ODBC format. It supplies leading zeros where necessary. For example, 2/29/2004 returns 2004-02-29 00:00:00.
A valid $HOROLOG integer date (0 through 2980013): Caché appends a time value of 00:00:00, then stores the resulting timestamp in ODBC format. For example, 59594 returns 2004-02-29 00:00:00. Note that a $HOROLOG date integer cannot have leading zeros.
A correctly formatted, but invalid, date (in ODBC or locale format) with no time value: Caché appends a time value of 00:00:00. It then stores the date portion as supplied. For example, 02/29/2003 returns 02/29/2003 00:00:00. 1776-07-04 returns 1776-07-04 00:00:00.
An incorrectly formatted and invalid, date (in ODBC, locale, or $HOROLOG format) with no time value: Caché returns the string “error”. For example, 2/29/2003 (no leading zero and invalid date value) returns “error”. 00234 ($HOROLOG with leading zeros) returns “error”

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()
  IF rset.%Next() {
  WRITE rset.ts1,!
  WRITE rset.ts2,!
  WRITE rset.ts3,!
  WRITE rset.ts4,!
  WRITE rset.ts5,!
  WRITE rset.ts6
  }