topic Comparing Dates For Incremental Load From Sql Server in QlikView

Comparing Dates For Incremental Load From Sql Server

Anonymous — Tue, 15 Oct 2013 16:24:01 GMT

I am having an issue comparing dates from a Sql Server database to pull most recent inserted and changed records.

Within my script I set my most recent updated/inserted date to the maximum value from the qvd. That value is 41569.498310185 which comes from the date 2013-10-22 11:57:34.000.

I then change a record on the sql server database to have a updated/inserted date two hours ahead of the previous max date. so it's value is 2013-10-22 13:57:34.000. I run the script again with the WHERE clause comparing my database date greater than or equal to the maximum value from the qvd. WHERE MySqlDate >= $(vLastExecTime). I keep setting the database date a little higher and it still doesn't pick it up. I end up with a date two days in advance. 2013-10-24 18:57:34.000 then it meets the condition and updates the qvd.

I assume there is some sort of date translation I need to do to the MySqlDate on the Where clause?

Thanks

Re: Comparing Dates For Incremental Load From Sql Server

vivientexier — Tue, 15 Oct 2013 16:53:11 GMT

Two days in advance ?! So you need a "vMin_time" and a "vMax_time".

Where MySqlDate >= $(vMin_time) and MySqlDate <= $(vMax_time)

Re: Comparing Dates For Incremental Load From Sql Server

Anonymous — Tue, 15 Oct 2013 16:57:12 GMT

Why would I need a max time? shouldn't I be able to use a Min Date/time and check for greater than or equal to? I want all records that that meet that criteria whether it's just a second greater but Qlikview doesn't seem to be that accurate with the date comparison in some way.

Re: Comparing Dates For Incremental Load From Sql Server

Anonymous — Thu, 17 Oct 2013 13:21:46 GMT

I found the answer to the problem. According to this post it is because of the different date starting points that QlikView and Sql Server use that the numerical date value is calculated on.

http://community.qlik.com/thread/13498