If the character string in a character-to-TIMESTAMP conversion omits the day, month, year, hour, minute, or second, the system uses default values for the target TIMESTAMP value.
IF the character string omits the … | THEN the system uses the … |
---|---|
day | value of 1 (the first day of the month). |
month | value of 1 (the month of January). |
year | current year. |
hour | value of 0. |
minute | |
second |
Consider the following table:
CREATE TABLE timestamp_log (id INTEGER, start_ts TIMESTAMP, end_ts TIMESTAMP);
The following INSERT statement converts two character strings to TIMESTAMP values. Both strings omit the hour, minute, and second. Additionally, the first character string omits the day and the second character string omits the month.
INSERT timestamp_log (1001 ,CAST ('January 2006' AS TIMESTAMP FORMAT 'MMMMBYYYY') ,CAST ('2006-01' AS TIMESTAMP FORMAT 'YYYY-DD'));
The result of the INSERT statement is as follows:
SELECT * FROM timestamp_log; id start_ts end_ts ----------- -------------------------- -------------------------- 1001 2006-01-01 00:00:00.000000 2006-01-01 00:00:00.000000
Here is an INSERT statement where both character strings omit the year. Additionally, the first character string omits the hour and the second character string omits the minute. Assume the current year is 2003.
INSERT timestamp_log (1002 ,CAST ('January 23 04:05' AS TIMESTAMP FORMAT 'MMMMBDDBMI:SS') ,CAST ('01-23 04:05' AS TIMESTAMP FORMAT 'MM-DDBHH:SS'));
The result of the INSERT statement is as follows:
SELECT * FROM timestamp_log WHERE id = 1002; id start_ts end_ts ----------- -------------------------- -------------------------- 1001 2003-01-23 00:04:05.000000 2003-01-23 04:00:05.000000