Date Conversion

Database
Enthusiast

Date Conversion

The date field is in the format 'DD-MON-YY'.

But wherein the data which i receive is as below.

EX:

12-Aug-12

1-Aug-12

The first if i handle using the substring the second value will be handled wrongly.

I want these values loaded as 'YYYY-MM-DD'

Can you please suggest how i can handle this in TD?

Tags (1)
9 REPLIES
Enthusiast

Re: Date Conversion

Internally, Teradata does not store Date as in any speicifc Date format, it stores Date value as a four-byte signed integer value dervied based on

(YEAR - 1900) * 10000 + (MONTH * 100) + DAY

Format only apply when you display the Date.

Enthusiast

Re: Date Conversion

Yes right...But the source file has the data as mentioned above. Where "DD" is coming as 1 and not "01".

And thats where am seeing the problem that my code is not working.

Code sample:

cast(cast(substr(cast(CREATED as char(9),1,7)||'20'||substr(cast(CREATED as char(9)),8,100) as date format 'dd-mmm-yyyy') as date format 'YYYY-MM-DD')

Enthusiast

Re: Date Conversion

Hi Mallesh,

You can simple check the length of incoming date string and based on that you can concatenate 0 in the start and make the string in a standard format for your rest of the code, like

SELECT CASE WHEN CHAR_LENGTH(CREATED) = 9 THEN CREATED ELSE '0'||CREATED END

You will always have the date in DD-MON-YY format.

Enthusiast

Re: Date Conversion

Here you go

SELECT '12-Aug-12' AS COL1 , CAST(CASE WHEN CHAR_LENGTH(SUBSTRING(COL1 FROM 1 FOR POSITION('-' IN COL1)-1))=1 THEN '0'||COL1 else COL1 end AS DATE FORMAT 'DD-MMM-YY') ;

SELECT '2-Aug-12' AS COL1 , CAST(CASE WHEN CHAR_LENGTH(SUBSTRING(COL1 FROM 1 FOR POSITION('-' IN COL1)-1))=1 THEN '0'||COL1 else COL1 end AS DATE FORMAT 'DD-MMM-YY') ;

It will solve your issue.

Enthusiast

Re: Date Conversion

sel case char('12-aug-12') when 9 then add_months((('12-aug-12')(format 'dd-mmm-yy',date)),12*100) end,
case char('1-aug-12') when 8 then add_months((('0'||('1-aug-12'))(format 'dd-mmm-yy',date)),12*100) end

Enthusiast

Re: Date Conversion

Hi, Harpreet singh

I'm new teradata , when i was executing below query i saw date

SELECT '12-Aug-12' AS COL1 , CAST(CASE WHEN CHAR_LENGTH(SUBSTRING(COL1 FROM 1 FOR POSITION('-' IN COL1)-1))=1 THEN '0'||COL1 else COL1 end AS DATE FORMAT 'DD-MMM-YY') ;

Result:

12-Aug-12    8/12/1912

I need 2012 instead of 1912.

Who can i change above query.Please suggest me.

Enthusiast

Re: Date Conversion

Hi Macktd,

there are several wasy to tackle it.  It happens because of setting in dbs control century break parameter where we customize Teradata that if user doesnt provide a century for date then what will be century by default. now coming to solutions:

1. Change DBS control parameter century break to default 21 century

2. In case its only for your specific process that days are always 2012 and not 1912 then add interval 100 years on top of casting function.

3. If it is year 50 to 99, you want it as 1950 to 1999 and later on 00 to 49 needed to be 2000 to 2049, then do a case on the substring of yy and add 100 years or not.

SELECT '12-Aug-12' AS COL1 ,

(CAST(CASE WHEN CHAR_LENGTH(SUBSTRING(COL1 FROM 1 FOR POSITION('-' IN COL1)-1))=1 THEN '0'||COL1 else COL1 end AS DATE FORMAT 'DD-MMM-YY') ) as col2,

case when substring(trim (Extract (year from col2)) from 3) >50

then col2

else col2 +INTERVAL '100' YEAR

end as finaldate

 COL1             col2                 finaldate

 12-Aug-12     08/12/1912       08/12/2012

Enthusiast

Re: Date Conversion

Thank you guys,

it worked...and finally we managed with the below on.

(CASE WHEN CHAR_LENGTH(CREATED) = 9

THEN

cast(cast(substr(cast(CREATED as char(9)),1,7)||'20'||substr(cast(CREATED as char(9)),8,11) as date format 'dd-mmm-yyyy') as date format 'YYYY-MM-DD')

ELSE

cast(cast(substr(cast('0'||CREATED as char(9)),1,7)||'20'||substr(cast('0'||CREATED as char(9)),8,11) as date format 'dd-mmm-yyyy') as date format 'YYYY-MM-DD') END ) as CREATED

Enthusiast

Re: Date Conversion

Thank you very much..harpreet singh Bhai........