Date Conversion Issue, from String to DATE

General
Enthusiast

Date Conversion Issue, from String to DATE

Hello, I fastloaded data into a temp table, and have a date columns as varchar(255), and am getting an error with insert/select to a perm table that has the receiving date column as dtcolname date format 'yyyy-mm-dd', and when I insert/select that column, I get error 2666 invalid date supplied on the source column. I've tried a variety if cast on date and formats, with this one CAST(DATECOL AS DATE) (FORMAT 'YYYY-MM-DD') (CHAR(10)) AS DATECOL being the one I thought would work, but I always get an error 2666. Any suggestions?

7 REPLIES
Teradata Employee

Re: Date Conversion Issue, from String to DATE

If the temp table VARCHAR column DATECOL contains valid date values in precisely the yyyy-mm-dd format, then

CAST(DATECOL AS DATE FORMAT 'yyyy-mm-dd')

Enthusiast

Re: Date Conversion Issue, from String to DATE

UPDATE: I went back to the Excel file, did a Custom Date Format, on the column in question, exported, floaded, and then did the ins/sel, with no errors, and with no cast/formats either. That is because in the exported flat file I made it YYYY-MM-DD spedifically. Originally I loaded the data and the date in the varchar column was 1/1/2015. and not soemthing like 01/01/2015, that missing leading zero and getting to a recognizable format was the issue, and I fixed it at the source data file creation instead of code gyrations to get it turned around. Thank you for the response Fred, you are right, if I had it truly in yyyy-mm-dd then the cast/format code line I had should have worked.

Re: Date Conversion Issue, from String to DATE

Hi Everyone,

may i know how the below statement is working.

I am not understand how it is working.

sel date'2017-01-01' + date'2017-01-01';

sel date'2017-01-01' + date'2017-01-01';

 *** Query completed. One row found. One column returned. 

 *** Total elapsed time was 1 second.

(2017-01-01+2017-01-01)

-----------------------

                1473272 <<<<<< Not able to understand date addition

sel date'2017-01-01' - date'2017-01-01';

sel date'2017-01-01' - date'2017-01-01';

 *** Query completed. One row found. One column returned. 

 *** Total elapsed time was 1 second.

(2017-01-01-2017-01-01)

-----------------------

                      0 <<<< ale to understand this subtraction

 BTEQ -- Enter your SQL request or BTEQ command: 

Any help can be appreciated....!

Thanks in Advance...!

Senior Apprentice

Re: Date Conversion Issue, from String to DATE

This result is really strange.

According to the SQL Functions & Operators manual DATE + DATE does not report an error, but results are generally not meaningful.

I never checked the actual result but assumed it's like casting both to integers (using the internal storage format) and then adding them. But this would result in

(2017-1900)*10000+01*100+01 = 1170101

1170101 + 1170101 = 2340202

Now 1473272 / 2 = 736636, which is close to the number of days since the minimum date in Teradata's calendar: DATE '2017-01-01' - DATE '0001-01-01' = 736329

1473272 - (736329*2) = 614, which seems to be a constant, for any dates the formula is:

(dateA + dateB) = (dateA - DATE '0001-01-01') + (dateB - DATE '0001-01-01') + 614

But don't ask why it's 614, as results are generally not meaningful, so it could be 42, too :-)

Re: Date Conversion Issue, from String to DATE

Thanks, Dnoeth .. for your explanation.

But it seems something strange

Re: Date Conversion Issue, from String to DATE

Hi All, i am facing issue in the date format. I have converted the string to date but the query fails BETWEEN DATE '2013-01-01' AND DATE '2014-01-01' with error message invalid date. Can some one help.

Senior Apprentice

Re: Date Conversion Issue, from String to DATE

You either got bad data or the format doesn't match.

Can you show some strings?