Convert date format m/d/yyyy to mm/dd/yyyy in TPT

Tools & Utilities
Enthusiast

Convert date format m/d/yyyy to mm/dd/yyyy in TPT

I have a file I am loading to a table in my database via TPT. Everything is working properly, however I am dropping records where the date format is m/d/yyyy (as opposed to mm/dd/yyyy). Is there a way, in the insert statement in APPLY to convert the field on the fly via CASE, CAST, or some string manipulation?  

My insert statement is pretty basic:

STEP INS_STAGE( APPLY ( 'INSERT INTO MYDB.MYTABLE (field1, field2) VALUES (:field1, :theDirtyDate);'))

I would attempt to clean this up on the CLI, but AWK is a deranged ape with a machine gun and I am a terrible handler, so I would prefer to not let it out of its cage. Plus the actual records are fairly large and contain all sorts of characters that would cause me to SPLIT and IF inside my AWK and I don't want to go there.

4 REPLIES
Enthusiast

Re: Convert date format m/d/yyyy to mm/dd/yyyy in TPT

Figured this one out after posting. I'm using the following:

CASE
WHEN (POSITION(''/'' IN :birthdate) = 3)
AND (POSITION(''/'' IN SUBSTRING(:birthdate FROM POSITION(''/'' IN :birthdate) + 1 FOR 3)) = 3)
THEN CAST(:birthdate AS DATE FORMAT ''MM/DD/YYYY'')
WHEN (POSITION(''/'' IN :birthdate) = 2)
AND (POSITION(''/'' IN SUBSTRING(:birthdate FROM POSITION(''/'' IN :birthdate) + 1 FOR 3)) = 2)
THEN CAST((''0'' || SUBSTRING(:birthdate FROM 1 FOR 2) || ''0'' || SUBSTRING(:birthdate FROM 3 FOR 6)) AS DATE FORMAT ''MM/DD/YYYY'')
WHEN (POSITION(''/'' IN :birthdate) = 3)
AND (POSITION(''/'' IN SUBSTRING(:birthdate FROM POSITION(''/'' IN :birthdate) + 1 FOR 3)) = 2)
THEN CAST((SUBSTRING(:birthdate FROM 1 FOR 3) || ''0'' || SUBSTRING(:birthdate FROM 4 FOR 6)) AS DATE FORMAT ''MM/DD/YYYY'')
WHEN (POSITION(''/'' IN :birthdate) = 2)
AND (POSITION(''/'' IN SUBSTRING(:birthdate FROM POSITION(''/'' IN :birthdate) + 1 FOR 3)) = 3)
THEN CAST((''0'' || SUBSTRING(:birthdate FROM 1 FOR 2) || SUBSTRING(:birthdate FROM 3 FOR 7)) AS DATE FORMAT ''MM/DD/YYYY'')
ELSE NULL
END,

and it's still more legible than AWK.

Junior Contributor

Re: Convert date format m/d/yyyy to mm/dd/yyyy in TPT

Check if your release of TPT supports VARDATE, defining your column as

col VARDATE(10) FORMATIN ('mm/dd/yyyy') FORMATOUT ('mm/dd/yyyy')

should allow single digit month/day.

Dieter

Fan

Re: Convert date format m/d/yyyy to mm/dd/yyyy in TPT

select cast(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_SUBSTR(trim('11/5/2002 12:00:00 PM ....blah'), '^([0-9]{1,2}\/){1,2}[0-9]{4}'), '^([0-9])\/', '0\1/'), '\/([0-9])\/', '/0\1/')
as date format 'mm/dd/yyyy') as date_in_the_string
Enthusiast

Re: Convert date format m/d/yyyy to mm/dd/yyyy in TPT

Hi, This might work.

Select '1/2/2014' 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 , 'YYYY/MM/DD'), 'MM/DD/YYYY') as "MM/DD/YYYY";

Thanks,

Dinesh