Incremental Load with Variables and SQL 2005 Urgent

[#1]

I seem to be having an issue with Incremental Load and using the Today() function with SQL 2005 database.

((changedate) >= $(LastExecTime) AND (changedate) < $(ThisExecTime))

the above is not working as this morning it did not load any changes made yesterday.

I am guessing that the variable:

Let ThisExecTime=(today());

is returning the date 5/2/2012 so I am assuming that the above (According to the log file)  formula is now showing:

((changedate) >= 5/1/2012 AND (changedate) < 5/2/2012)

Now, I am guessing that qlikview does not see the above BOLDS as dates when it runs the script, but it does not produce an error and loads everything from the QVD File just fine.

In the Qlikview Reference document it says to use:

ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#

When I try the above: ((changedate) >= #$(LastExecTime)# AND (changedate) < #$(ThisExecTime)#)

((changedate) >= #5/1/2012# AND (changedate) < #5/2/2012#)

The script Fails with just an error in the log

General Script Error

5/2/2012:       Execution Failed

5/2/2012:      Execution finished.

Next I tried to tell it that the variable is a date using:

Let ThisExecTime= 'Date('&(today())&')';

However I still get the same above error, and am guessing it is because Date() is not recognized in SQL 2005

Does anyone have any ideas on what I should do here? Somewhat urgent as without this worjking 2 dashboards are now displaying the wrong data results.

[#2 · Accepted solution]

I have also just tried this:

((CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) >= CAST((  STR( ( 2012 ) ) + '/' +  STR( (5) ) + '/' +  STR((1) )  ) AS DATETIME)  AND (CAST((  STR( ( YEAR(changedate) ) ) + '/' +  STR( MONTH(changedate) ) + '/' +  STR( DAY(changedate) )  ) AS DATETIME) ) <= CAST((  STR( ( 2012 ) ) + '/' +  STR( (May) ) + '/' +  STR((2) )  ) AS DATETIME) ) 

Trying to convert to SQL terms so the format is the same, however it still fails with just a General Script Error. Really wish this error was more descriptive.

Edit: Just noticed in the log that it saved the variable month(today()) as the monthname MAY. changed it to use num(month(today())) and it is working.