Character Strings That Omit Day, Month, Year, Hour, Minute, or Second - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantage™

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