All Forums Database
goldminer 82 posts Joined 05/09
29 Dec 2010
Convert varchar(8) to date in select statement

I would like to convert a varchar(8) field to a date field in a select statement. I have tried a number of different statements and non of them seem to work. For the statements that I think should work, I keep getting the error message:

2666: Invalid date supplied for

The syntax I think should work is:

SELECT CAST(column_name AS DATE FORMAT 'YYYY-MM-DD')
from databasename.tablename sample 10;

column_name is defined as varchar(8) in the source table.

Has anyone else run into this issue, and if so, were you able to solve?

Thanks!

NetFx 282 posts Joined 09/06
29 Dec 2010

Is it VarChar(8) or VarChar(10)?
The format mentioned in the post is for VarChar(10).
Try something like
select cast('12199931' as DATE format 'MMYYYYDD');

goldminer 82 posts Joined 05/09
29 Dec 2010

The source data column is coming in as '19981001' (yyyymmdd). When I run the following SQL I get the same error:

2666: Invalid date supplied for tablename.columnname

Select cast(columnnames DATE format 'yyyymmdd')
from databasename.tablename sample 10;

NetFx 282 posts Joined 09/06
29 Dec 2010

I just tried the following on Teradata 13.0;

create table TestCastDate (C01 Integer, C02 VarChar(8));
Insert Into TestCastDate values (1, '19981001');
Insert Into TestCastDate values (2, '19981002');
select CAST(C02 as DATE Format 'YYYYMMDD') from TestCastDate;

I suggest checking the actual data in the source column.

goldminer 82 posts Joined 05/09
29 Dec 2010

Thanks NetFx... upon further examination, I found 00000000 values in the column which was causing the error.

You must sign in to leave a comment.