0

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.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Cos
  • 1,649
  • 1
  • 27
  • 50
  • Put two zeros in front of the year? – Sneftel Jul 29 '14 at 08:36
  • How? Also, some of the timestamps I work with start with 4-digit-years, and some with 2-digit-years. And I want to make a join on this field, so too much fiddling with it would cost much time – Cos Jul 29 '14 at 11:05
  • I don't know much about BODS, but according to the [reference guide](http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_reference_en.pdf), **rrrr** is not a valid date format specifier. – Joe Jul 29 '14 at 12:03
  • `regex_replace('01.04.29 16:45:04.400909000','[:. ]','')` should replace the `:`, `.` and the space ` `, with the empty string `''`. – Luuk May 26 '23 at 07:07

0 Answers0