TO_TIMESTAMP (SQL)
Synopsis
TO_TIMESTAMP(dateString,format)
TO_TIMESTAMP(dateString)
Description
The TO_TIMESTAMP function converts date and time strings in various formats to the standard InterSystems IRIS® representation of a timestamp. The returned timestamp is of data type TIMESTAMP and has this format, with leading zeros included and a 24-hour clock time:
yyyy-mm-dd hh:mi:ss
The returned timestamp includes leading zeros and uses a 24-hour clock time by default.
TO_TIMESTAMP supports fractional seconds, two-digit to four-digit year conversions, and 12-hour to 24-hour clock time conversions. It provides range validation of date and time element values, including leap year validation. Range validation violations generate an SQLCODE -400 error.
TO_TIMESTAMP returns a standard timestamp in ODBC format. To return an encoded 64-bit timestamp, use TO_POSIXTIME instead. For details on other SQL functions that perform conversions, see Related SQL Functions.
-
TO_TIMESTAMP(dateString,format) converts the date string using the specified format string. The date and time elements of dateString must be compatible with the format elements in the corresponding positions of format.
This statement converts a string that specifies the date from the previous syntax in a different format.
SELECT TO_TIMESTAMP('June 29, 2022 12:34 PM', 'MONTH DD, YYYY')
Examples:
-
TO_TIMESTAMP(dateString) converts the date string using the default format of DD MON YYYY HH:MI:SS. The dateString argument must be compatible with this format. If you omit the time, or specify only a portion of the time, TO_TIMESTAMP returns the time as 00:00:00.
This statement converts the date string 29 Jun 2022 to the timestamp 2022-06-29 12:34:00.
SELECT TO_TIMESTAMP('29 Jun 2022 12:34')
Arguments
dateString
The dateString argument is a string expression that specifies the date string to be converted to a timestamp. dateString can contain a date value, a time value, or both.
Each character of dateString must correspond to the format string, with the following exceptions:
-
Leading zeros can be included or omitted, with the exception of a dateString without separator characters.
-
Years can 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 to match format MONTH. It accepts the initial characters 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”, and “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, but “Febs” is not. Month names are not case-sensitive.
-
Time values can be specified 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 zeros. By default, a timestamp is returned without fractional seconds.
If dateString omits components of the timestamp, TO_TIMESTAMP supplies the missing components:
-
If you specify a date without a time, TO_TIMESTAMP sets the returned time value to 00:00:00. If you omit only a portion of the time, TO_TIMESTAMP sets that portion to 00. Fractional seconds are supported but must be explicitly specified.
-
If you specify a time without a date, TO_TIMESTAMP sets the returned date value to 01-01 (January 1) of the current year. If you omit only the day, the day defaults to 01. If you also omit the month, the month and day default to 01-01
-
If you omit the year, the year defaults to 00, which is expanded to a four-digit year according to the year element of format. If you also omit the year in format, YYYY defaults to the current year.
You can specify TO_TIMESTAMP dates from January 1, 0001 to December 31, 9999. To represent earlier dates, use the TO_DATE function.
format
The format argument is a date and time string that specifies the format of the dateString. TO_TIMESTAMP converts the date elements of dateString according to the format elements in the corresponding positions of format. The elements of format follow these 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 dateString. 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 (National Language Support) 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 assumes a DD value of 01. In these formats, you specify leading zeros for all elements, with the exception of the final element.
-
Invalid format elements in format are ignored.
This table lists the valid date and time format elements. The uppercase form is shown, but these elements are not case-sensitive.
Element | Meaning |
---|---|
DD | Two-digit day of month (01-31). Leading zeros are not required, unless format contains no date separator characters. |
MM |
Two-digit month number (01-12; 01 = January). Leading zeros are not required, unless format contains no date separator characters. In Japanese and Chinese, a month number consists of a numeric value followed by the ideogram for “month”. |
MON | Abbreviated name of month, as specified by the MonthAbbr property in the current locale. By default, in English, this is the first three letters of the month name. In other locales, month abbreviations can be more than three letters long and might not consist of the first letters of the month name. A period character is not permitted. Not case-sensitive. |
MONTH | Full name of the month, as specified by the MonthName property in the current locale. Not case-sensitive. |
YYYY | Four-digit year in the range 0001 to 9999. |
YY | Last two digits of the year. The first two digits of a two-digit year default to 19. |
RR / RRRR | Two-digit year to four-digit year conversion. For more details, see Two-Digit Year Conversion (RR and RRRR Formats). |
DDD | Day of the year. The count of days since January 1. For more details, Day of the Year Conversion (DDD Format). |
HH | Hour (1–12 or 00–23), depending on whether a meridian indicator (AM or PM) is specified. Can be specified as HH12 or HH24. |
MI | Minute, specified as 00–59. |
SS | Second, specified as 00–59. |
FF | Fractions of a second. TO_TIMESTAMP returns the exact fractional value specified in dateString, without padding or truncating the value. To specify FF, you must provide a decimal separator format character (.). If you do not specify FF, fractional seconds specified in dateString are ignored. |
AM PM A.M. P.M. |
Meridian indicator specifying a 12-hour clock time. The TIMESTAMP data type always represents time using a 24-hour clock. A dateString can represent time using a 12-hour clock or a 24-hour clock. TO_TIMESTAMP assumes a 24-hour clock, unless the time part of dateString ends with a meridian indicator. For example:DD MON YYYY HH:MI:SS.FF P.M. This format supports 12-hour clock values specified in dateString such as 2:23pm, 2:23:54.6pm, 2:23:54 pm, 2:23:54 p.m., and 2:23:54 (assumed to be AM). If you specify periods in the meridian indictors, you must separate the indicator from the time with at least one space. |
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. However, these format elements are not validated or used to determine the return value. For more details on these format elements, see TO_CHAR.
If you omit format, TO_TIMESTAMP parses the date string using the default format of DD MON YYYY HH:MI:SS. For example, '01 Feb 3456 07:08:09'.
Examples
Convert Date Strings to Multiple Timestamp Formats
This statement specifies date strings in various formats. The first one uses the default format, the others specify a format argument that TO_TIMESTAMP uses to parse the date string. TO_TIMESTAMP converts all these date strings to the timestamp 2022–06–29 00:00:00.
SELECT
TO_TIMESTAMP('29 JUN 2022'),
TO_TIMESTAMP('2022 Jun 29','YYYY MON DD'),
TO_TIMESTAMP('JUNE 29, 2022','month dd, YYYY'),
TO_TIMESTAMP('2022***06***29','YYYY***MM***DD'),
TO_TIMESTAMP('06/29/2022','MM/DD/YYYY'),
TO_TIMESTAMP('29/6/2022','DD/MM/YYYY')
This statement specifies the YYYYMM date format. It does not require element separators. TO_TIMESTAMP supplies the missing day and time values and returns the timestamp 2022–06–01 00:00:00.
SELECT TO_TIMESTAMP('202206','YYYYMM')
This statement specifies just the HH:MI:SS.FF time format. TO_TIMESTAMP supplies the missing date value, returning in all cases a date value of YYYY–01–01, where YYYY is the current year. The time value varies based on the fractional seconds specified in the date string. TO_TIMESTAMP passes fractional seconds through exactly as specified, with no padding or truncation.
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')
This statement shows other ways to specify a time format with fractional seconds. All three calls to TO_TIMESTAMP return an ODBC-format timestamp with the time portion value as 11:34:22.9678. In the first two calls, the omitted date portion defaults to January 1 of the current year. The third call specifies a date portion.
SELECT TO_TIMESTAMP('113422.9678','HHMISS.FF'),
TO_TIMESTAMP('9678.113422','FF.HHMISS'),
TO_TIMESTAMP('9678.20220629113422','FF.YYYYMMDDHHMISS')
Set Default Timestamp Column Values
TO_TIMESTAMP can supply a default timestamp value to columns in a table. For example, this statement creates a table that accepts default values for ReviewDate, a column of type TIMESTAMP.
CREATE TABLE Sample.MyEmpReviews (
EmpNum INTEGER UNIQUE NOT NULL,
ReviewDate TIMESTAMP DEFAULT TO_TIMESTAMP(365,'DDD'))
If you insert a row without specifying a ReviewDate value, then the ReviewDate is set to the default timestamp of the 365th day of the current year.
INSERT INTO Sample.MyEmpReviews (EmpNum) VALUES (1)
You can use TO_TIMESTAMP to set default column values in both CREATE TABLE and ALTER TABLE ADD COLUMN statements. When setting these defaults, dateString must be a literal value.
Two-Digit Year Conversion (RR and RRRR Formats)
The YY format converts a two-digit year value to four digits by appending 19. For example, 07 becomes 1907 and 93 becomes 1993. The RR and RRRR formats provide more flexible two-digit to four-digit year conversions.
The RR format conversion is based on the current year.
-
If the current year is in the first half of a century:
-
Two-digit years from 00 through 49 are expanded to a four-digit year in the current century.
-
Two-digit years from 50 through 99 are expanded to a four-digit year in the previous century.
This statement shows the display format of timestamps that TO_TIMESTAMP returns when the current year is between 2000 and 2050.
SELECT TO_TIMESTAMP('29 September 00','DD MONTH RR'), -- 2000-09-29 00:00:00 TO_TIMESTAMP('29 September 18','DD MONTH RR'), -- 2018-09-29 00:00:00 TO_TIMESTAMP('29 September 49','DD MONTH RR'), -- 2049-09-29 00:00:00 TO_TIMESTAMP('29 September 50','DD MONTH RR'), -- 1950-09-29 00:00:00 TO_TIMESTAMP('29 September 77','DD MONTH RR') -- 1977-09-29 00:00:00
-
-
If the current year is in the second half of a century, all two-digit years are expanded to a four-digit year in the current century.
This statement shows the display format of dates that TO_TIMESTAMP returns when the current year is between 2050 and 2099.
SELECT TO_TIMESTAMP('29 September 00','DD MONTH RR'), -- 2000-09-29 00:00:00 TO_TIMESTAMP('29 September 21','DD MONTH RR'), -- 2021-09-29 00:00:00 TO_TIMESTAMP('29 September 49','DD MONTH RR'), -- 2049-09-29 00:00:00 TO_TIMESTAMP('29 September 50','DD MONTH RR'), -- 1950-09-29 00:00:00 TO_TIMESTAMP('29 September 77','DD MONTH RR') -- 1977-09-29 00:00:00
Using the RRRR format, you can input a mix of two-digit and four-digit years. TO_TIMESTAMP passes four-digit years through unchanged. TO_TIMESTAMP converts two-digit years to four-digit years by using the RR format algorithm described earlier in this example.
This statement shows the display format of dates that TO_TIMESTAMP returns when the current year is between 2000 and 2050.
SELECT
TO_TIMESTAMP('29 September 2021','DD MONTH RRRR'), -- 2021-09-29 00:00:00
TO_TIMESTAMP('29 September 21','DD MONTH RRRR'), -- 2021-09-29 00:00:00
TO_TIMESTAMP('29 September 1949','DD MONTH RRRR'), -- 1949-09-29 00:00:00
TO_TIMESTAMP('29 September 49','DD MONTH RRRR'), -- 2049-09-29 00:00:00
TO_TIMESTAMP('29 September 1950','DD MONTH RRRR'), -- 1950-09-29 00:00:00
TO_TIMESTAMP('29 September 50','DD MONTH RRRR') -- 1950-09-29 00:00:00
Day of the Year Conversion (DDD Format)
You can use the DDD format to convert the day of the year (that is, the number of days elapsed since January 1) to an actual timestamp. To perform this conversion:
-
The date portion of the format argument must contain the DDD format element and optionally a year format such as YYYY, YY, RR, or RRRR. You can specify these elements in any order but they must include a separator character between them. If you omit the year element, then TO_TIMESTAMP defaults to the current year.
-
The dateString argument must contain corresponding day and year values, where:
-
day is an integer in the range 1 through 365 (366 if year is a leap year).
-
year is a year in the range 0001 through 9999.
-
This statement returns the 60th day of the year 2022.
SELECT TO_TIMESTAMP('2022:60','YYYY:DDD') --2022-03-01 00:00:00
TO_TIMESTAMP passes month elements through unchanged. If a format string contains both a DD and a DDD element, TO_TIMESTAMP processes the DDD element and ignores the DD element. For example, this statement returns a timestamp for the date 2/29/2020 (the 60th day of 2020), not for 12/31/2020:
SELECT TO_TIMESTAMP('2020-12-31-60','YYYY-MM-DD-DDD')
TO_TIMESTAMP returns a timestamp expression containing the day of the year, not the day of the year itself. To return this day value, use TO_CHAR.
More About
Alternatives
To perform equivalent timestamp conversions in ObjectScript, use the TOTIMESTAMP()Opens in a new tab method:
$SYSTEM.SQL.Functions.TOTIMESTAMP(date_string,format)
See Also
-
SQL commands: CREATE TABLE, ALTER TABLE
-
SQL functions: CAST, CONVERT, TO_CHAR, TO_DATE, TO_NUMBER, TO_POSIXTIME