FORMAT m/d/yy column as DD/MM/YYYY

UDA
Enthusiast

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 example
1st 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.
4 REPLIES
Teradata Employee

Re: FORMAT m/d/yy column as DD/MM/YYYY

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

Enthusiast

Re: FORMAT m/d/yy column as DD/MM/YYYY

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

Re: FORMAT m/d/yy column as DD/MM/YYYY

Hi

What version of teradata you are using.

I guess Teradata v12 will return date format as m/dd/yy.

Thanks
Sen
Enthusiast

Re: FORMAT m/d/yy column as DD/MM/YYYY

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