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. 
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.

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

You may take a look at this thread:

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


krishna1985 13 posts Joined 08/16
21 Aug 2016

thank all worked like charm L:)

Hari Krishna

You must sign in to leave a comment.