cast as date

Database

cast as date

Hello,

I have a field defined like that : LAUFD DECIMAL(8,0).
In that field, I have for example this value : 20040924
How can I convert this value to a date : 24-09-2004
If I do :
select LAUFD, cast(LAUFD as date) from tst_load.MHND
I obtain a strange date in 3904 :

LAUFD LAUFD
20040924 24.09.3904

Can someone help me ?
Thanks a lot.
Emilie
3 REPLIES

Re: cast as date

You have two problems:
1) Standard SQL recognizes only the 'yyyy-mm-dd' format form ISO-8601 so this is what you suoudl be using.
2) CAST( AS DATE) needs a string expression.

Cast the DECIMAL to a CHAR(9), then add the hyphens, then finally cast it as a DATE. This extra work is what happens when you do not use the right data type from the start.
SN
Enthusiast

Re: cast as date

On the same note....you can use this stmt

sel Substr((Cast(LAUFD As char(8))),7,2)||'-'||Substr((Cast(LAUFD As char(8))),5,2)||'-'||
Substr((Cast(LAUFD As char(8))),1,4)

** you will have the output in the format 'DD-MM-YYYY', but as CHAR and not as DATE data type.
If you need the output to be a DATE dat type, then rearrange the substr such that you concatenate in the format 'YYYY-MM-DD'
and cast to DATE.

-SN
Junior Contributor

Re: cast as date

Teradata stores dates using following formula:
(year - 1900) * 10000 + month * 100 + day

It's a bit strange, but working :-)

So the easiest way to cast your Decimal is CAST(20040924 - 19000000 AS DATE)

Dieter