Qlikview converts date to nvarchar when calling SQL procedure

[#1]

Hi Guys,

I'm calling a SQL procedure from Qlikview.  The SQL procedure has two input variables (@Start and @End).  I have a Start and End input boxes in Qlikview. My code is:

     ODBC CONNECT TO Trial_Database;

     SQL EXEC [Trial_Database].[dbo].[RunAll] @Start = '$(Start)', @End = '$(End)';

Now the problem is that when loading this procedure, I get the error:

     Error converting data type varchar to date.

     SQL EXEC [Trial_Database].[dbo].[RunAll] @Start = '01/01/2013', @End = '41820'

My question is that why does Qlikview convert the @End variable to the integer equivalent of the date?  Anybody know any fixes?

Thanks in advance.

Joel

[maxgro]

before you call the proc

let NewStart=date($(Start));

let NewEnd=date($(End));

trace $(NewStart)  $(NewEnd);

ODBC CONNECT TO Trial_Database;

SQL EXEC [Trial_Database].[dbo].[RunAll] @Start = '$(Start)', @End = '$(End)';

if the NewDate isn't in the correct SQL format you can format in this way adding the 2nd param to date function (adapt YYYYMMDD)

let NewStart=date($(Start), 'YYYYMMDD');

[#3]

Hi Massimo,

I tried your coding but now, the problem is reversed.  The variable @End is fixed but @Start is not fixed.

The variable for @Start = 1/1/2013 but when I use the date function on @Start, it return 12/30/1899. 

Also, when I open the variable overview, I can see that the variable name End has a value 41820 while the value Start has a value 01/01/2013.  Any thoughts?

[hic]

The select statement that is sent to the ODBC driver is a string. Hence, it is not possible to send anything but characters.

To make sure that the dollar expansion of the variables generates the correct characters, you should make sure that the variable has a text representation that is the one you want. Hence, you should - just as Massimo suggests - use the Date() function to create the format the database expects when you create the variables.

See Data Types in QlikView, The Date Function

HIC

[#5]

Thanks guys for this.  I really appreciate the help.

Joel