Character Strings That Omit Day, Month, Year, Hour, Minute, or Second

Teradata Vantage™ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

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