How to get 0001 instead of 2001 using YYYY

149 Views Asked by At

I have a column with different timestamps, like:

5771.10.04 16:07:23.800913000
0967.06.17 06:20:28.800906000
3857.06.18 03:49:03.800906000
01.04.29 16:45:04.400909000

I need to convert these into decimals (which I use for a join of some million rows), like so:

57711004160723800913
9670617062028800906
38570618034903800906
10429164504400909

I do it using this function:

cast(substr(to_char($timestamp,'YYYYMMDDHH24MISSFF'),1,20), 'decimal(20,0)');

The problem is the last timestamp, which only has two digits for the year, where the YYYY conversion occurs. I would need the 01 to be transformed into 0001, instead it is transformed into 2001.

Any ideas how I could solve this in a quick/non-intensive way?

Tl;dr I need to transform the year 01 into 0001 instead of 2001 in BODS.

0

There are 0 best solutions below