InterSystems SQL Reference
TO_TIMESTAMP
|
|
A date function that converts a formatted string to a timestamp.
Synopsis
TO_TIMESTAMP(date_string[,format])
The
TO_TIMESTAMP function converts date and time strings in various formats to a standard timestamp, with data type TIMESTAMP.
TO_TIMESTAMP returns a timestamp with the following format:
with leading zeroes always included. Time is specified using a 24–hour clock. By default, a returned timestamp does not include fractional seconds.
If
date_string omits components of the timestamp,
TO_TIMESTAMP supplies the missing components. If both
date_string and
format omit the year, yyyy defaults to the current year; if only
date_string omits the year, it defaults to 00, which is expanded to a four-digit year according to the year
format element. If a day or month value is omitted, dd defaults to 01; mm-dd defaults to 01-01. Therefore, if both
date_string and
format omit if the date component of a timestamp,
TO_TIMESTAMP defaults to January 1 of the current year, in ODBC format: yyyy-01-01.
A missing time component defaults to 00. Fractional seconds are supported, but must be explicitly specified; no fractional seconds are provided by default.
TO_TIMESTAMP supports conversion of two-digit years to four digits.
TO_TIMESTAMP supports conversion of 12-hour clock time to 24-hour clock time. It provides range validation of date and time element values, including leap year validation. Range validation violations generate an SQLCODE -400 error.
This function can also be invoked from ObjectScript using the
TOTIMESTAMP() method call:
The
TO_TIMESTAMP function can be used in data definition when supplying a default value to a timestamp field. For example:
CREATE TABLE Sample.MyEmpReviews
(EmpNum INTEGER UNIQUE NOT NULL,
ReviewDate TIMESTAMP DEFAULT TO_TIMESTAMP(365,'DDD'))
In this example, the user inserting a record can either suppler a ReviewDate value, supply no ReviewDate value and get the default timestamp of the 365th day of the current year, or supply a ReviewDate of NULL and get NULL.
-
TO_TIMESTAMP converts a formatted date and time string to a standard timestamp.
-
TO_CHAR performs the reverse operation; it converts a standard timestamp to a formatted date and time string.
-
TO_DATE converts a formatted date string to a date integer.
-
CAST and
CONVERT perform TIMESTAMP data type conversion.
The
date_string argument specifies a date and time string literal. If you supply a date string with no time component,
TO_TIMESTAMP supplies the time value 00:00:00. If you supply a time string with no date component,
TO_TIMESTAMP supplies the date of 01–01 (January 1) of the current year.
You can supply a date and time string of any kind for the input
date_string. Each
date_string character must correspond to the
format string, with the following exceptions:
-
Leading zeros may be included or omitted (with the exception of a
date_string without separator characters).
-
Years may be specified with two digits or four digits.
-
Month abbreviations (with
format MON) must match the month abbreviation for that locale. For some locales, a month abbreviation may not be the initial sequential characters of the month name. Month abbreviations are not case-sensitive.
-
Month names (with
format MONTH) should be specified as full month names. However,
TO_TIMESTAMP does not require full month names with
format MONTH; it accepts the initial character(s) of the full month name and selects the first month in the month list that corresponds to that initial letter sequence. Therefore, in English, “J” = “January”, “Ju” = “June”, “Jul” = “July”. All characters specified must match the sequential characters of the full month name; characters beyond the full month name are not checked. For example, “Fe”, “Febru”, and “FebruaryLeap” are all valid values; “Febs” is not a valid value. Month names are not case-sensitive.
-
Time values can be input with the time separator characters defined for the locale. The output timestamp always represents the time value with the ODBC standard time separator characters: colon (:) for hours, minutes, and seconds, and period (.) for fractional seconds. An omitted time element defaults to zeroes. By default, a timestamp is returned without fractional seconds.
A
format is a string of one or more format elements specified according to the following rules:
-
Format elements are not case-sensitive.
-
Almost any sequence or number of format elements is permitted.
-
Format strings separate their elements with non-alphanumeric separator characters (for example, a space, slash, or hyphen) that match the separator characters in the
date_string. These separator characters do not appear in the output string, which uses standard timestamp separators: hyphens for date values, colons for time values, and a period (when required) for fractional seconds. This use of separator characters does not depend on the DateSeparator defined for your NLS locale.
-
The following date format strings do not require separator characters:
MMDDYYYY,
DDMMYYYY,
YYYYMMDDHHMISS,
YYYYMMDDHHMI,
YYYYMMDDHH,
YYYYMMDD,
YYYYDDMM,
HHMISS, and
HHMI. The incomplete date format
YYYYMM is also supported, and assume a DD value of 01. Note that in these cases leading zeros must be provided for all elements (such as MM and DD), with the exception of the final element.
-
Characters in
format that are not valid format elements are ignored.
The following table lists the valid date format elements for the
format argument:
A
TO_TIMESTAMP format can also include a D (day of week number), DY (day of week abbreviation), or DAY (day of week name) element to match the input
date_string. However, these format elements are not validated or used to determine the return value. For further details on these
format elements, refer to
TO_CHAR.
Two-Digit Year Conversion (RR and RRRR formats)
The RR format provides two-digit to four-digit year conversion. This conversion is based on the current year. If the current year is in the first half of a century (for example, 2000 through 2050), two-digit years from 00 through 49 are expanded to a four-digit year in the current century, and two-digit years from 50 through 99 are expanded to a four-digit year in the previous century. If the current year is in the second half of a century (for example, 2050 through 2099), all two-digit years are expanded to a four-digit year in the current century. This expansion of two-digit years to four-digit years is shown in the following example:
SELECT TO_TIMESTAMP('29 September 00','DD MONTH RR'),
TO_TIMESTAMP('29 September 18','DD MONTH RR'),
TO_TIMESTAMP('29 September 49','DD MONTH RR'),
TO_TIMESTAMP('29 September 50','DD MONTH RR'),
TO_TIMESTAMP('29 September 77','DD MONTH RR')
The RRRR format permits you to input a mix of two–digit and four-digit years. Four-digit years are passed through unchanged (the same as YYYY). Two-digit years are converted to four-digit years, using the RR format algorithm. This is shown in the following example:
SELECT TO_TIMESTAMP('29 September 2018','DD MONTH RRRR')AS FourDigit,
TO_TIMESTAMP('29 September 18','DD MONTH RRRR') AS TwoDigit,
TO_TIMESTAMP('29 September 1949','DD MONTH RRRR') AS FourDigit,
TO_TIMESTAMP('29 September 49','DD MONTH RRRR') AS TwoDigit,
TO_TIMESTAMP('29 September 1950','DD MONTH RRRR') AS FourDigit,
TO_TIMESTAMP('29 September 50','DD MONTH RRRR') AS TwoDigit
Day of the Year (DDD format)
You can use DDD to convert the day of the year (number of days elapsed since January 1) to an actual date. The format string
DDD YYYY must be paired with a corresponding
date_string consisting of an integer number of days and a four-digit year. (Two-digit years must be specified as RR (not YY) when used with DDD.) The format string
DDD defaults to the current year. The number of elapsed days must be a positive integer in the range 1 through 365 (366 if YYYY is a leap year). The four-digit year must be within the standard InterSystems IRIS date range: 1841 through 9999. (If you omit the year, it defaults to the current year.) The DDD and year (YYYY, RRRR, or RR) format elements can be specified in any order; a separator character between them is mandatory; this separator can be a blank space. The following example shows this use of Day of the Year:
SELECT TO_TIMESTAMP('2018:160','YYYY:DDD')
If a format string contains both a DD and a DDD element, the DDD element is dominant. This is shown in the following example, which returns 2008-02-29 00:00:00 (not 2008-12-31 00:00:00):
SELECT TO_TIMESTAMP('2018-12-31-60','YYYY-MM-DD-DDD')
TO_TIMESTAMP permits you to return a date expression corresponding to a day of the year.
TO_CHAR permits you to return the day of the year corresponding to a date expression.
TO_TIMESTAMP cannot represent a date before December 31, 1840. Attempted to input such a date results in an SQLCODE -400 error.
TO_POSIXTIME can represent dates back to January 1, 0001. It can include time values with up to 6 digits of fractional seconds precision.
TO_DATE provides a Julian date format than can represent dates back to January 1, 4712 BCE. Julian date conversion converts a seven-digit internal positive integer value (a Julian day count) to a display-format or ODBC-format date. Time values are not supported for Julian dates.
A TIMESTAMP always represents time using a 24-hour clock. A
date_string may represent time using a 12-hour clock or a 24-hour clock.
TO_TIMESTAMP assumes a 24-hour clock, unless one of the following applies:
-
The
date_string time value is followed by 'am' or 'pm' (with no periods). These meridian indicators are not case-sensitive, and may be appended to the time value, or be separated from it by one or more spaces.
-
The following embedded SQL example specifies date strings in various formats. The first one uses the default format, the others specify a
format. All of these convert
date_string to the timestamp value of
2018–06–29 00:00:00:
&sql(SELECT
TO_TIMESTAMP('29 JUN 2018'),
TO_TIMESTAMP('2018 Jun 29','YYYY MON DD'),
TO_TIMESTAMP('JUNE 29, 2018','month dd, YYYY'),
TO_TIMESTAMP('2018***06***29','YYYY***MM***DD'),
TO_TIMESTAMP('06/29/2018','MM/DD/YYYY'),
TO_TIMESTAMP('29/6/2018','DD/MM/YYYY')
INTO :a,:b,:c,:d,:e,:f)
IF SQLCODE=0 { WRITE !,a,!,b,!,c,!,d,!,e,!,f }
ELSE { WRITE "SQLCODE error:",SQLCODE }
The following example specifies the YYYYMM date format. It does not require element separators.
TO_TIMESTAMP supplies the missing day and time values:
SELECT TO_TIMESTAMP('201806','YYYYMM')
The following example specifies just the HH:MI:SS.FF time format.
TO_TIMESTAMP supplies the missing date value. In each case, this example returns the date of
2018–01–01 (where 2018 is the current year):
SELECT TO_TIMESTAMP('11:34','HH:MI:SS.FF'),
TO_TIMESTAMP('11:34:22','HH:MI:SS.FF'),
TO_TIMESTAMP('11:34:22.00','HH:MI:SS.FF'),
TO_TIMESTAMP('11:34:22.7','HH:MI:SS.FF'),
TO_TIMESTAMP('11:34:22.7000000','HH:MI:SS.FF')
Note that fractional seconds are passed through exactly as specified, with no padding or truncation.
The following example shows some other ways to specify a time format with fractional seconds:
SELECT TO_TIMESTAMP('113422.9678','HHMISS.FF'),
TO_TIMESTAMP('9678.113422','FF.HHMISS'),
TO_TIMESTAMP('9678.20170804113422','FF.YYYYMMDDHHMISS')
All three invocations of
TO_TIMESTAMP return an ODBC-format timestamp with the time portion value as
11:34:22.9678. For the first two, the omitted date portion defaults to January 1 of the current year; the third supplies a date portion value.