Year date formatting in BO Data Services

[Former Member]

Hi guys,

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 a decimals (which I use for a join of some million rows), like so:

57711004160723800913

9670617062028800906

38570618034903800906

10117121104600914

And I do this using this function (which is already time-intensive as it is):

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

[Former Member]

Hi,

Left padding zeros not possible when the datatype is integer or decimal. You need to change the datatype to varchar.

Arun

[Former Member]

But after the to_char conversion, before I get a chance to add any padding, the '2001' is already written there. How can I turn this into varchar without adding the 20 at the front?

[former_member186897]

Use the date formatting to change this into two digit year and then do as you want.

[Former Member]

Then I would lose the first two digits of all other timestamps that start with a 4-digit-year.

[Former Member]

Add a query transform and convert all the date to varchar. Then apply the cast and use the target datatype as varchar.