All Forums Database
krishna1985 13 posts Joined 08/16
18 Aug 2016
Padding and timestamp

Hi All,
 
I have a column with the format
column x and beow are the values. 
10/1/15
4/1/16
7/1/15
I want to convert the same into 2015-01-10 00:00:00 and same for others. I want to pad month  AND date to 0 if its single digit. I tried run the beow substring first
x= Trim(20||substring( FDOE_QTR  from 6 for 2))|| '-' ||trim(substring(FDOE_QTR from 4 for 1))||'-'||substring(FDOE_QTR  from 1 for 2)
which changed to 2015-1-10 but I am not able to pad the MONTH and DATE with '0'. Can you please let me know how to do the same?
 
Also I was looking for something single script which converts the same in timestamp(6) format.
 
Thanks 

Regards, Hari Krishna
CarlosAL 512 posts Joined 04/08
19 Aug 2016

Hi.
You may take a look at this thread:
http://forums.teradata.com/forum/database/converting-string-to-date-using-regexp-getting-error-3798
HTH.
Cheers.
Carlos.

dnoeth 4628 posts Joined 11/04
19 Aug 2016

You can use a Regular Expression to add the missing zeroes:
 REGEXP_REPLACE(FDOE_QTR, '\b([0-9])\b', '0\1')
see: http://forums.teradata.com/forum/enterprise/convert-varchar-to-timestamp#comment-147303
 
Depending on your CenturyBreak settings this might return a 1915 instead of 2015, you can add another regex to add the missing 20:
REGEXP_REPLACE(REGEXP_REPLACE(ts, '\b([0-9])\b', '0\1'), '([0-9]{2})$', '20\1')
 
Now you got a string with the correct format and then you apply a typcast:
CAST(REGEXP_REPLACE(REGEXP_REPLACE(ts, '\b([0-9])\b', '0\1'), '([0-9]{2})$', '20\1') AS TIMESTAMP(0) FORMAT 'dd/mm/yyyy')
 
 

Dieter

krishna1985 13 posts Joined 08/16
21 Aug 2016

thank all ...it worked like charm L:)
 

Regards,
Hari Krishna

You must sign in to leave a comment.