0
297

Written by

Carl Tawn
Follow
Question Carl Tawn · Sep 17, 2019

DATEPART local date

#Ensemble

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)0
Log in or sign up to continue

Comments

Vitaliy Serdtsev · Sep 17, 2019

At me in SMP the following query returns 23:

<FONT COLOR="#0000ff">select </FONT><FONT COLOR="#808000">datepart</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'hh'</FONT><FONT COLOR="#000000">, {</FONT><FONT COLOR="#000080">ts </FONT><FONT COLOR="#008080">'2019-09-10 23:01:45'</FONT><FONT COLOR="#000000">})</FONT>
0
Roger Merchberger  Sep 17, 2019 to Vitaliy Serdtsev

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!

0
Carl Tawn  Sep 18, 2019 to Vitaliy Serdtsev

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
0
Vitaliy Serdtsev  Sep 18, 2019 to Carl Tawn

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

Then so:

<FONT COLOR="#808000">datepart</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'hh'</FONT><FONT COLOR="#000000">, %external(</FONT><FONT COLOR="#008000">TimeCreated</FONT><FONT COLOR="#000000">))

See Data Display Options

0
Carl Tawn  Sep 19, 2019 to Vitaliy Serdtsev

That did the trick, thanks.

0