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