Varchar to date conversion - interesting observation

Database
Enthusiast

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.
8 REPLIES
WAQ
Enthusiast

Re: Varchar to date conversion - interesting observation

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')
Enthusiast

Re: Varchar to date conversion - interesting observation

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

Re: Varchar to date conversion - interesting observation

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
Junior Contributor

Re: Varchar to date conversion - interesting observation

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
Teradata Employee

Re: Varchar to date conversion - interesting observation

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.

Re: Varchar to date conversion - interesting observation

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

Teradata Employee

Re: Varchar to date conversion - interesting observation

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.

Re: Varchar to date conversion - interesting observation

Hola muchas gracias dnoeth, Saludos.