Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Round timestamp - Data Services 3.2

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
Former Member replied

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.

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question