topic Re: Datetimeoffset field in Qlikview in Water Cooler

Datetimeoffset field in Qlikview

Nino — Fri, 18 Jan 2019 10:22:20 GMT

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.

Re: Datetimeoffset field in Qlikview

Gysbert_Wassenaar — Fri, 18 Jan 2019 10:55:21 GMT

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

Re: Datetimeoffset field in Qlikview

Nino — Fri, 18 Jan 2019 11:15:54 GMT

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.

Re: Datetimeoffset field in Qlikview

Gysbert_Wassenaar — Fri, 18 Jan 2019 12:43:33 GMT

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.

Re: Datetimeoffset field in Qlikview

Nino — Fri, 18 Jan 2019 13:24:04 GMT

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

Re: Datetimeoffset field in Qlikview

Nino — Fri, 18 Jan 2019 13:47:40 GMT

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.

Re: Datetimeoffset field in Qlikview

Alie — Fri, 01 Nov 2019 12:36:38 GMT

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