Carl Tawn · Sep 17, 2019

DATEPART local date


I'm trying to run a query from Management Portal, to get a count of messages in 10 minute periods for analysis. I'm using DATEPART to pull the hour and minute portions, but the value being returned is the UTC value, rather than the actual value.


TimeCreated = '2019-09-10 23:01:45'

DATEPART(hh, TimeCreated)   is returning 22 rather than 23. 


How do i force DATEPART to return the correct display value?

3 0 5 116
Log in or sign up to continue


At me in SMP the following query returns 23:

select datepart('hh', {ts '2019-09-10 23:01:45'})

And, at the terminal prompt:  W $SYSTEM.SQL.DATEPART("hh","2019-09-10 23:01:55") also returns '23' - and I'm GMT-4 currently...

Hope this helps!

Hi, thanks for the reply.

{ts} only seems to work with literal values, not with columns. So the following query errors:

select %nolock top(1) datepart('hh', {ts TimeCreated}) 
from Ens.MessageHeader

That did the trick, thanks.