All Forums Database
Malleshkr 11 posts Joined 08/12
18 Oct 2012
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?
 

_Mallesh
Tags:
pawan0608 101 posts Joined 12/07
18 Oct 2012

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.

Malleshkr 11 posts Joined 08/12
18 Oct 2012

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

_Mallesh

Qaisar Kiani 337 posts Joined 11/05
18 Oct 2012

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.

Harpreet Singh 101 posts Joined 10/11
18 Oct 2012

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.

Mathuram 21 posts Joined 09/12
18 Oct 2012
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
macktd 23 posts Joined 09/12
18 Oct 2012

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.

Harpreet Singh 101 posts Joined 10/11
18 Oct 2012

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
 

Malleshkr 11 posts Joined 08/12
19 Oct 2012

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

_Mallesh

macktd 23 posts Joined 09/12
19 Oct 2012

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

You must sign in to leave a comment.