QlikView vs SQL Server Date

[#1]

I am in the process of writing a generic script for incrementally loading and updating qvd files based on a date field and I have come accross some unusual behaviour in the way QlikView and SQL Server convert numeric dates.

MaxLoad:

LOAD max(ModifyDate) as MaxF

FROM $(vLoadFile) (qvd);

Let vMAX = peek('MaxF', 0, 'MaxLoad');

'$(vDTQVD)':

Load

*;

SQL SELECT *

FROM $(vTable) WHERE $(ModifyDate) > $(vMAX);;

'$(vDTQVD)':

LOAD * FROM $(vfLoadFile)(qvd)

WHERE NOT EXISTS ( [$(vIDField)] );

When I started to run this code against a changing data set, noticed that I was dropping records and on debugging found the following behaviour:-

vMAX = 40408.194

SQL Server interprets this as

SELECT CAST(40408.194 AS DATETIME) FROM vTable returns 2010-08-20 04:39:21.600

but QlikView returns 2010-08-18 04:39:21.600

Any feedback would be fantastic as the numeric method feels so much cleaner than formating the dates as text.

Isabel

[Miguel_Angel_Baeyens · Accepted solution]

Hello Isabel,

Indeed, QliKView internally uses date format counting since 31/12/1899 (1) and so on. Since some timestamp include time and date, there you have those decimals in the QlikView date. This will likely help

LOAD Ceil(max(ModifyDate)) as MaxFFROM $(vLoadFile) (qvd);

Anyway, a precedent load script would help, so the dates are stored as integers in your QVD files:

Load Date(DateField, 'DD/MM/YYYY') AS DateField;SQL SELECT *FROM $(vTable) WHERE $(ModifyDate) > $(vMAX);

When you store it now, all values will be properly formatted. You can duplicate fields should you want to keep dates and times in one field but use the other to storeing and loading purposes.

Hope that helps.

[#3]

Hi Miguel,

Thanks for the quick response, knowing that QlikView's date starting point is different to SQL Server means I can adjust the date by adding 2.

Thanks again!

Isabel