All Forums UDA
deagle 17 posts Joined 11/08
08 Apr 2009
FORMAT m/d/yy column as DD/MM/YYYY

Hi all,I have a column coming down from an As400 extract. the format of this date column is m/d/yy, take the following date example1st of April 2009 ---> 4/1/09 on extract.I want to format this field as 'DD/MM/YYYY'.I keep getting an invalid date error.Any comments will help!!Thanks guys.

Adeel Chaudhry 773 posts Joined 04/08
09 Apr 2009

Hello,As per my knowledge, Teradata doesn't support single digit date values i.e. D, M, 1/2/2009, etc.Your best option will be to do some pre-processing on such fields and make them 2 digit date values i.e. 01/02/2009.HTH!Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

Jimm 298 posts Joined 09/07
22 Apr 2009

The following will reformat your date column to a standard dd/mm/yy.If you are loading via Multiload or TPump, you can use it in the load. With Fastload, you have to take it in as character and use SQL to reformat it.CASE WHEN Substr(InDate,3,1) = '/' AND Substr(InDate,6,1) = '/' -- Format is dd/mm/yy THEN InDate WHEN Substr(InDate,2,1) = '/' AND Substr(InData,5,1) = '/' -- Format is d/mm/yy THEN '0'||Substr(InDate,1,7) WHEN Substr(InDate,2,1) = '/' AND Substr(InDate,4,1) = '/' -- Format is d/m/yy THEN '0'||Substr(InDate,1,2)||'0'||Substr(InDate,3,4) WHEN Substr(InDate,3,1) = '/' AND Substr(InDate,5,1) = '/' -- Format is dd/m/yy THEN Substr(InDate,1,3)||'0'||Substr(InDate,4,4) ELSE NULL END (Char(8)) (Date,Format 'dd/mm/yy')(InDate is the name of your column or input variable)

Senth 3 posts Joined 04/09
23 Apr 2009

Hi What version of teradata you are using.I guess Teradata v12 will return date format as m/dd/yy.ThanksSen

dins2k2 51 posts Joined 05/13
20 Nov 2015

Hi ,
 
This should work.
 
Select '1/2/14' as date1,
TO_CHAR(TO_DATE (
case   
    when strtok(date1, '/', 2) between 1 and 9 then strtok(date1,'/', 3)||'/0'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)
    else  strtok(date1, '/', 3)||'/'||strtok(date1, '/', 1)||'/'||strtok(date1,'/', 2)
end , 'YY/MM/DD'), 'DD/MM/YYYY') as "DD/MM/YYYY";
 
Thanks,
Dinesh

You must sign in to leave a comment.