topic Re: datetime offset in App Development

datetime offset

barry_stevens — Tue, 12 Sep 2017 22:33:44 GMT

Hi All,

Firstly apologies if this has been asked before or if asking in the wrong group.

We have a field coming through and seen as as text in the qlik table and I am told this is a datetime offset in the db '2017-06-21 08:37:16.4613064 +12:00' what is the best way to convert this to a datetime in qlik - is it possible in the data manager or is this a must do in the data load script

Regards

Barry

Re: datetime offset

karthikoffi27se — Wed, 13 Sep 2017 05:07:05 GMT

Hi Barry,

You can do it in load script

Please use DATE("YOURFIELD",'DD/MMM/YYYY') as YOURFIELD

This will convert all of your field to date format.

Many Thanks

Karthik

Re: datetime offset

barry_stevens — Wed, 13 Sep 2017 23:27:42 GMT

Thanks, how does this handle the +12 hour offset?

Re: datetime offset

rahulwankhede — Thu, 14 Sep 2017 07:18:23 GMT

Hi Barry,

You can try this,

For Datetime

(left(Date,19))

For date

date(left(Date,19))

hope that will help you.

Re: datetime offset

Anonymous — Thu, 14 Sep 2017 08:57:45 GMT

Maybe something like :

= timestamp(

timestamp#(

left( '2017-06-21 08:37:16.4613064 +12:00' , 19 ) , 'YYYY-MM-DD hh:mm:ss' )

+ num(interval#(right( '2017-06-21 08:37:16.4613064 +12:00' , 5 ) , 'hh:mm' ))

* num(mid( '2017-06-21 08:37:16.4613064 +12:00' , 29 ,1 ) & '1')

, 'YYYY-MM-DD hh:mm:ss' )

But I'd suggest you give this really good test, especially for negative offsets