DATEPART local date

Question

Question

Carl Tawn · Sep 17, 2019

Hi,

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.

i.e.

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?

Discussion (5)1

Log in or sign up to continue

Vitaliy Serdtsev · Sep 17, 2019

At me in SMP the following query returns 23:

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

0 0

score 0 · Answer 1 · 2019-09-17T12:48:26-04:00

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!

score 0 · Answer 2 · 2019-09-18T03:56:13-04:00

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

score 0 · Answer 3 · 2019-09-18T04:27:46-04:00

The field TimeCreated is of type Ens.DataType.UTC.

Then so:

datepart('hh', %external(TimeCreated))

See Data Display Options

score 0 · Answer 4 · 2019-09-19T03:21:36-04:00

Carl Tawn · Sep 19, 2019

That did the trick, thanks.

0 0