topic Re: Importing a SQL date with a timezone in QlikView

Importing a SQL date with a timezone

Tue, 18 Nov 2014 10:03:48 GMT

Hey Guys,

The dates in my SQL database are in the following syntax:

2014-10-27 06:59:59.1708129 +01:00

2014-09-16 15:41:15.4797236 +02:00

2014-09-17 13:05:58.4830392 +02:00

I thought I figured it out, but then I noticed some numbers were off. The problem is that the following line in my load script does not recognize the dates with time +01:00:

SET DateFormat='YYYY-MM-DD hh:mm:ss.fffffff +02:00';

How can I change my DateFormat so that it supports both timezones?

Regards,

Sijmen

Re: Importing a SQL date with a timezone

marcus_malinow — Tue, 18 Nov 2014 10:16:04 GMT

Hi Sijmen,

the DateFormat variable shows the default format for displaying dates within QlikView, not necessarily the format of dates that are to be imported.

I'd suggest converting all your times to a standard time zone within your load. The alt() function will allow you to import differing date formats easily.

For example

alt(

date#(SQLDate, 'YYYY-MM-DD hh:mm:ss.ffffffff +00.00'),

date#(SQLDate, 'YYYY-MM-DD hh:mm:ss.ffffffff +01.00') + 1/24,

etc....

) as UTCDate

Re: Importing a SQL date with a timezone

marcus_malinow — Tue, 18 Nov 2014 10:29:33 GMT

actually, on second thoughts, rather than an enormous alt() statement,

how about :

date#(left(SQLDate, 19)) + num(right(SQLDate, 6))/24

Re: Importing a SQL date with a timezone

Tue, 18 Nov 2014 14:15:17 GMT

Hey, thanks for your response.

I tried that alt() statement, it crashed Qlik two times in a row, I guess that is not really an option.

The second option seems better, however I can not get it to work. I would like it to be imported as a date from the start, how do I use that in my SQL import?

I now got the following import statement:

Faults:

SQL SELECT CreatedAt,

EntityId,

FaultUrgencyId,

Id as FaultId

FROM "MyDatabase".dbo.Faults

WHERE CreatedAt > '2014-01-01 00:00:00 +02:00';

CreatedAt contains the dates that I want to import properly, where do I put your statement to get it as a date in the table?

Regards,

Sijmen

Re: Importing a SQL date with a timezone

Tue, 18 Nov 2014 15:19:54 GMT

Hi,

You have the timezone() function available in qlikview

Re: Importing a SQL date with a timezone

MarcoWedel — Tue, 18 Nov 2014 16:13:10 GMT

ConvertToLocalTime(Timestamp#(Left('2014-10-27 06:59:59.1708129 +01:00',Index('2014-10-27 06:59:59.1708129 +01:00',' ',-1)-1),'YYYY-MM-DD hh:mm:ss[.fffffff]'),'GMT'&SubField('2014-10-27 06:59:59.1708129 +01:00',' ',-1))

replace '2014-10-27 06:59:59.1708129 +01:00' with your timestamp field.

hope this helps

regards

Marco