Importing a SQL date with a timezone

[#1]

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

[marcus_malinow]

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

[marcus_malinow]

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

how about :

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

[#4]

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

[#5]

Hi,

You have the timezone() function available in qlikview

[MarcoWedel]

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