convert varchar to timestamp

UDA
Enthusiast

Re: convert varchar to timestamp

Hi All,

Need help with the above issue.

Junior Contributor

Re: convert varchar to timestamp

Enthusiast

Re: convert varchar to timestamp

Thanks Dieter!

Faced more issues with it.

Here is what I was able to finally do with my data to get it correct. Would really appreciate if you could tell me any better ways.

The date field here contains dates like:

23/12/2013

2/9/2013

12/8/2014

8/11/2010

CASE 
WHEN LENGTH(STRTOK(Effective_Date,'/',2)) = 1 AND LENGTH(STRTOK(Effective_Date,'/',1)) = 2 THEN REGEXP_REPLACE(Effective_Date, '([0-9])/', '0\1/',1,2,'c')
WHEN LENGTH(STRTOK(Effective_Date,'/',2)) = 2 AND LENGTH(STRTOK(Effective_Date,'/',1)) = 1 THEN REGEXP_REPLACE(Effective_Date, '([0-9])/', '0\1/',1,1,'c')
WHEN LENGTH(STRTOK(Effective_Date,'/',2)) = 1 AND LENGTH(STRTOK(Effective_Date,'/',1)) = 1 THEN REGEXP_REPLACE(Effective_Date, '([0-9])/([0-9])', '0\1/0\2')
ELSE Effective_Date
END AS Effective_Date
Junior Contributor

Re: convert varchar to timestamp

What's your TD release? Did you try the SQL from the other thread?

TD15: REGEXP_REPLACE('2/3/2013', '\b([0-9])\b', '0\1')

TD14: REGEXP_REPLACE('2/3/2013','\b([0-9])\b', '0\1', 1, 0, 'c')