Padding and timestamp


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.



Re: Padding and timestamp


Re: Padding and timestamp

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

Re: Padding and timestamp

thank all worked like charm L:)