All Forums Database
zenith 10 posts Joined 12/10
07 Feb 2011
Varchar to date conversion - interesting observation

A staging teradata table (stage_db.table1) is having a varchar(20) column (column_A)with values like:

09-FEB-09
22-NOV-00
?
02-JUL-96

I'm trying to create another table with a date column and want to load above values into it :

CT sandbox_db.date_experiment
(
date_experiment_dt DATE

)
PRIMARY INDEX( date_experiment_dt );

INSERT INTO sandbox_db.date_experiment
SELECT CAST (column_A AS DATE FORMAT 'yyyy-mm-dd')
FROM stage_db.table1

but getting error :2666 - Invalid date supplied for table1.column_A

Can I get a helping hand ? Why I'm getting this error and whats the possible remedy.

WAQ 158 posts Joined 02/10
07 Feb 2011

Thats probably because you are providing a wrong format of date for your input data. Try this:

SELECT CAST (column_A AS DATE FORMAT 'DD-MMM-YY')

zenith 10 posts Joined 12/10
08 Feb 2011

No improvement , still the same error - 2666 - Invalid date supplied for table1.column_A
Any more suggestions pls...

solinrf 3 posts Joined 12/10
08 Feb 2011

I think the null value (?) will also give you a problem with the insert/cast. Maybe filter those out in a where clause ...
where column_a is not null

dnoeth 4628 posts Joined 11/04
08 Feb 2011

The NULL doesn't cause any issue, the format is correct, too, it's your data, which is faulty.

A common solution to this problem is to avoid the typecast on column_a:
SELECT column_a, calendar_date
FROM table1 LEFT JOIN sys_calendar.calendar
ON column_a = TRIM(calendar_date (FORMAT 'dd-mmm-yy'))
AND calendar_date BETWEEN DATE '1950-01-01' AND DATE '2049-12-31'

When you add "WHERE calendar_date IS NULL" you can see the bad values causing the error.

There's another advantage of this approach, you can specify the range of valid dates within your query and then it's independend of the Century Break setting of your system.

Dieter

Dieter

DMich 1 post Joined 01/11
08 Feb 2011

The earlier post using format 'dd-mmm-yy' should have worked. The only other problem is, perhaps, your century break setting for 2-digit years. With a century break of 40, for instance, a 'yy' value of less than 40 would be interpreted as being 2000 + 'yy' where-as 40 or greater would be 1900 + 'yy'. This might be causing a leap year problem for Feb 29. Nulls should not cause a problem except to tell you you can not store a null into a not null column. Other than this, you likely have an invalid date in your source data. Dnoeth's suggestion is a good one also.

babuji9791 1 post Joined 02/14
04 Feb 2014

I am facing the above issue which is mentioned by zenith. i have tried with the all possible scenarios which was provided above. there is no improve.
Now i got the solution. its working fine now.
 
Sol: just put one cast in the select part.
INSERT INTO sandbox_db.date_experiment
SELECT CAST (column_A as DATE FORMAT 'dd-mmm-yy')
FROM stage_db.table1

Adeel Chaudhry 773 posts Joined 04/08
05 Feb 2014

2-digit year is always tricky to handle. Causing issues with leap-year-february, or confusion between year 20** or 19**. Do check your data after insert if its correct.

-- If you are stuck at something .... consider it an opportunity to think anew.

FernandoRios 1 post Joined 06/15
18 Aug 2015

Hola muchas gracias dnoeth, Saludos.

You must sign in to leave a comment.