Casting TIMESTAMP(6) to DATE

Database
Enthusiast

Casting TIMESTAMP(6) to DATE

Hi guys,

 

I am having some problems when it comes to inserting a casted timestamp to a date value into a table with date format:

 

show table TSTML.TB1_TST;

--DTTST1 DATE FORMAT 'yyyy-mm-dd'
--DTTST2 DATE FORMAT 'yyyy-mm-dd'

CREATE MULTISET TABLE TSTML.TB2_TST AS (SELECT 
DTTST1 (FORMAT 'yyyy-mm-dd') AS DTTST1, 
DTTST2 (FORMAT 'yyyy-mm-dd') AS DTTST2
FROM TSTML.TB1_TST) WITH NO DATA;
--step 2 show table TSTML.TB2_TST; -- DTTST1 DATE FORMAT 'YY/MM/DD', -- DTTST2 DATE FORMAT 'YY/MM/DD',

show table NEWSCH.TB3_TST;
-- NEWDT_1 TIMESTAMP(6)
-- NEWDT_2 TIMESTAMP(6)

TIMESTAMP(6) --step 3 INSERT INTO TSTML.TB2_TST( DTTST1, DTTST2 ) SELECT CAST(A.NEWDT_1 AS DATE FORMAT 'YYYY-MM-DD') AS DTTST1, CAST(A.NEWDT_2 AS DATE FORMAT 'YYYY-MM-DD') AS DTTST2 FROM NEWSCH.TB3_TST A; INSERT INTO TSTML.TB1_TST SELECT * FROM TSTML.TB2_TST;

On step 3, it shows an error message:

 

Insert Statement failed.  Failed [5404 : HY000] Datetime field overflow. 

 

Any inputs?

 

Thanks, 


Accepted Solutions
Teradata Employee

Re: Create Table as Select with wrong format

The explanation for 5404 says, "The evaluation of a <datetime value expression> results in a value outside of the permissible range for any contained field or the result is invalid based on the natural rules for dates and times."

There is nothing wrong with casting a timestamp(6) as a date.  The only explanation I can imagine then is that there is something weird about one of those timestamps.   What do you get if you select Max and Min NEWDT_1 and NEWDT_2?

1 ACCEPTED SOLUTION
5 REPLIES
Teradata Employee

Re: Create Table as Select with wrong format

If you are inserting a date column from one table to another, and they are defined as dates in both tables, then it is superfluous to specify the format.  A date is a date, period, and format only matters when it's in character form.  The database does not store it in character form.

 

Your step3 actually has two steps.  Since the first one is explicitly casting A.NEWDT1 and -2 as DATEs, I am guessing these are character fields.  Either there is bad data, or you are not specifying the format that these character values actually have.  For this insert, it doesn't matter what format the target table dates are defined with; it only matters what format the character fields are in.  Once the character field is converted to a date, it is just a date.  When casting a char to a date, you have to define the format the char actually has, not the format you wish it had.

Enthusiast

Re: Create Table as Select with wrong format

GJColeman,

 

Thanks for the tips so far, but both NEWDT_1 and NEWDT_2 are TIMESTAMP(6) and I am still getting the Failed [5404 : HY000] Datetime field overflow. error message.

 

Kind regards,

Teradata Employee

Re: Create Table as Select with wrong format

The explanation for 5404 says, "The evaluation of a <datetime value expression> results in a value outside of the permissible range for any contained field or the result is invalid based on the natural rules for dates and times."

There is nothing wrong with casting a timestamp(6) as a date.  The only explanation I can imagine then is that there is something weird about one of those timestamps.   What do you get if you select Max and Min NEWDT_1 and NEWDT_2?

Teradata Employee

Re: Create Table as Select with wrong format

Generally this happens when there is a mismatch between session and system time zones and high dates or low dates are included in the data. The date will be cast to time stamp at the session time zone and then it will be stored into the table at the system timezone that was used to create the table. That can result in adding hours to high dates or subtracting hours from low dates putting them outside the range for time stamp.
Enthusiast

Re: Create Table as Select with wrong format

GJColeman,

 

Someone has changed the column type while I was testing it and that's why I got that message.

 

Everything is just fine now.


Thanks,