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?
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')
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.
may i know how the below statement is working.
I am not understand how it is working.
Any help can be appreciated....!
Thanks in Advance...!
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 :-)
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.