Datetimeoffset field in Qlikview

[Nino]

Hello!

I have a datetimeoffset SQL fields and I have to manage it, but neither the Day, Month and Year nor the Hour, Minute and Second functions work.

Could someone help me about the handling of this field or indicate me where I can find some documentation about?

Thank you.

N.

[Gysbert_Wassenaar]

What kind of values does that field contain? Are they unix epoch timestamps or datetime values or strings?

[Nino]

I'd like to obtain an intervaltime of 26 hours (24hours for the difference between 1900-01-01 and 1900-01-02 + 2hours for the day 1900-01-02).

The value is stored in a DateTimeOffset SQL database.

Thanks.

N.

[Gysbert_Wassenaar]

Looks like it's stored as a datetime aka timestamp value. Qlikview should load it as a numeric value. Check in Qlikview what the num() function returns for that value: num(DTO).

In general, if you want the difference between two timestamps you subtract one from the other. Then using the interval function you have the option to specify a format string to format the result in days and/or hours and/or minutes and/or seconds.

[Nino]

I'm sorry but you're wrong: the Num(DTO) function returns the "-" value, so I think it's not stored as numeric value.

[Nino · Accepted solution]

LOAD OffsetDateQlik-Date('01/01/1900','DD/MM/YYYY') As SpanTime;
SQL SELECT convert(datetime,OffsetDateSQL) As OffsetDateQlik
FROM MyTable;

SpanTime could be represented as IntervalTime. 😉

Thank you for the attention.

N.

[Alie]

Hi, I hope someone can explain this to me.

I have a db change where the date fields are now OffsetDateSQL: 2017-12-13 09:00:00.0000000 +01:00

09:00 seems to be the local or system time. I had in QV:
LOAD
DATE( FLOOR(ResocStartDate),'DD-MM-YYYY')  AS   %Datum
FROM mytable.qvd

But this does not work anymore. What should i change to get it working?
(I'm using Qlikview for windows 11)

Thanks very much for helping me