TO_TIMESTAMP (SQL)

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.

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.

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'.

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')

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.

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

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.

• 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.