All Forums Tools
jnevill 17 posts Joined 03/12
07 Oct 2013
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.
 

jnevill 17 posts Joined 03/12
07 Oct 2013

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.

dnoeth 4628 posts Joined 11/04
07 Oct 2013

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

Dieter

DanB 3 posts Joined 12/09
31 Jul 2015
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

 

dins2k2 51 posts Joined 05/13
20 Nov 2015

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
 

You must sign in to leave a comment.