Character Strings That Omit Day, Month, Year, Hour, Minute, or Second - Teradata Vantage - Analytics Database

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.

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