Skip to main content

Date and Time Constructs (SQL)

Validates and converts an ODBC date, time, or timestamp.

Synopsis

{d 'yyyy-mm-dd'}
{d nnnnnn}

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

{ts 'yyyy-mm-dd [hh:mm:ss.fff]'}
{ts 'mm/dd/yyyy [hh:mm:ss.fff]'}
{ts nnnnnn}

Description

These constructs take either an integer or a string in ODBC date, time, or timestamp format and convert it to the corresponding InterSystems IRIS 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 InterSystems IRIS $HOROLOG date format as an integer count value from 1840-12-31. InterSystems IRIS does not append a default time value. To support dates earlier than 1840-12-31 you must define the date field in the table with data type %Library.Date(MINVAL=-nnn), where the MINVAL is a negative number of days counting backwards from 1840-12-31 (day 0) to a maximum of -672045 (0001-01-01).

If you supply:

  • An integer less than -672045 (0001-01-01) or greater than 2980013 (9999-12-31) generates an SQLCODE -400 <VALUE OUT OF RANGE> error.

  • An invalid date (such as a date not in ODBC format or the date 02-29 in a non-leap year): InterSystems IRIS generates an SQLCODE -146 error: “yyyy-mm-dd' is an invalid ODBC/JDBC Date value”.

  • An ODBC timestamp value: InterSystems IRIS 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 InterSystems IRIS $HOROLOG time format as an integer count of seconds from midnight, with the specified fractional seconds. InterSystems IRIS Display mode and ODBC mode do not display the fractional seconds; the fractional seconds are truncated from these display formats.

If you supply:

  • An integer less than 0 (00:00:00) or greater than 86399.99 (23:59:59.99) generates an SQLCODE -400 <ILLEGAL VALUE> error.

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

  • An ODBC timestamp value: InterSystems IRIS 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 positive or negative integer date (-672045 through 2980013): InterSystems IRIS appends a time value of 00:00:00, then stores the resulting timestamp in ODBC format. For example, 64701 returns 2018-02-22 00:00:00. This is a valid $HOROLOG date integer. $HOROLOG 0 is 1840-12-31.

  • A valid timestamp in ODBC format: InterSystems IRIS stores the supplied value unchanged This is because InterSystems IRIS timestamp format is the same as ODBC timestamp format.

  • A valid timestamp using the locale default date and time formats (for example, 2/29/2016 12:23:46.77): InterSystems IRIS 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): InterSystems IRIS returns the string “error” as the value.

  • A valid date (in ODBC or locale format) with no time value: InterSystems IRIS 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/2016 returns 2016-02-29 00:00:00.

  • A correctly formatted, but invalid, date (in ODBC or locale format) with no time value: InterSystems IRIS appends a time value of 00:00:00. It then stores the date portion as supplied. For example, 02/29/2019 returns 02/29/2019 00:00:00.

  • An incorrectly formatted and invalid, date (in ODBC, locale, or $HOROLOG format) with no time value: InterSystems IRIS returns the string “error”. For example, 2/29/2019 (no leading zero and invalid date value) returns “error”. 00234 ($HOROLOG with leading zeros) returns “error

See $HOROLOG 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 64701. This example displays %SelectMode 0 (logical) values:

  SET myquery = 2
  SET myquery(1) = "SELECT {d '2018-02-22'} AS date1,"
  SET myquery(2) = "{d '2018-2-22'} 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 '2018-02-22 01:43:38'} AS ts1,"
  SET myquery(2) = "{ts '2018-02-22'} AS ts2,"
  SET myquery(3) = "{ts '02/22/2018 01:43:38.999'} AS ts3,"
  SET myquery(4) = "{ts '2/22/2018 01:43:38'} AS ts4,"
  SET myquery(5) = "{ts '02/22/2018'} AS ts5,"
  SET myquery(6) = "{ts '64701'} 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
  }
FeedbackOpens in a new tab