Round timestamp - Data Services 3.2

[Former Member]

Hi Guys,

I have a timestamp which I would like to round to nearest whole hour.

So

2010.05.12 22:38:20.000000000

becomes

2010.05.12 23:00:00.000000000

I havent found any time-functions, only date options seems to be available.

Does anybody have good way of achiving this ?

Best Regards

Rasmus

[Former Member · Accepted solution]

hi,

Create a time characteristic in BW and map it to the timestamp.

It would pick the hrs, minutes & sec. From the DSO to cube write a routine to pick only the hrs value into the cube time characteristic. You will have to use ABAP to achieve this.

regards,

Arvind.

[Former Member · Accepted solution]

Hi Arvind,

This is not BW question.

The question regards how to resolve it in "SAP BO Data Services".

Br Rasmus

[Former Member · Accepted solution]

Hi Arvind

The solution below takes advantage of the fact that the to_char() will truncate a datetime if not all components of the datetime are given.

to_date( to_char( sysdate() + num_to_interval( 30, 'M') , 'YYYY-MM-DD HH24' ), 'YYYY-MM-DD HH24')

So breaking it down...

sysdate() + num_to_interval( 30, 'M')

adds 30 minutes to the datetime, keeping the datetime within the current hour or taking it over into the next hour.

then

to_char( sysdate() + num_to_interval( 30, 'M') , 'YYYY-MM-DD HH24' )

truncates the result to the hour with zero minutes / seconds.

Then finally...

to_date( to_char( sysdate() + num_to_interval( 30, 'M') , 'YYYY-MM-DD HH24' ), 'YYYY-MM-DD HH24')

converts the truncated datetime back to a datetime format.

Obviously substitute your datetime value into this.

[Former Member · Accepted solution]

Thanks a lot, it worked like a charm.

Br Rasmus