SQL Query Error

[techvarun]

Hi Folks,

                  I am trying to Load Conditional data based on fromdate and todate from SQL server through DatePicker Object.

But when try to load the script

"SQL SELECT * FROM Persons WHERE DOB Between 'date(date#(99999999 - $(StartDate),'YYYYMMDD'))' and 'date(date#(99999999 - $(EndDate),'YYYYMMDD'))';"

it is showing the following error

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near 'YYYYMMDD'.

SQL SELECT * FROM Persons WHERE DOB Between 'date(date#(99999999 - 41082,'YYYYMMDD'))' and 'date(date#(99999999 - 41083,'YYYYMMDD'))'

Please help me to sort out the error in the SQL Query

Thanks in Advance

[tresesco]

Hello Varun,

QV functions can't be used under SQL command. You have to use some SQL date function. Otherwise you can load data into qv once then try to filter using qv functions like:

Load * Where Date(.....)>'....';

SQL Select * from <DB>;

Hope this helps

[hic]

The SELECT statement is evaluated by the OLE DB provider, not by QlikView. So, the QlikView Date() and Date#() functions are not available.

HIC

[swuehl]

Whatever you write after the SQL SELECT will be sent to the DBMS and will not interpreted by QV (with the exception below).

You are using functions like date() that are not known by the DBMS (and the sequence of single quotes is also problematic here).

I am not sure what you want to achieve, but you can try to create two variables in your QV script (before the LOAD / SQL Select statement) that will evaluate your two dates and then use dollar sign expansion to include the values in your SELECT sent to the DMBS.

A variable dollar sign expansion is executed before the string is sent.

[techvarun]

Tried this and now working fine.

LET VDate1 = Date($(StartDate),'YYYY-MM-DD');

LET VDate2 = Date($(EndDate),'YYYY-MM-DD');

SQL SELECT * FROM Persons WHERE DOB Between '$(VDate1)' and '$(VDate2)';

Regards

[techvarun]

Thank you all for the help

Regards

[techvarun]

Thanks HIC

[er_mohit]

Try this

load * where DOB>=$(StartDate) and DOB<=$(EndDate);

"SQL SELECT * FROM Persons;