topic Re: Qlikview converts date to nvarchar when calling SQL procedure in QlikView

Qlikview converts date to nvarchar when calling SQL procedure

Sun, 06 Sep 2015 07:00:12 GMT

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

Re: Qlikview converts date to nvarchar when calling SQL procedure

maxgro — Sun, 06 Sep 2015 07:19:37 GMT

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');

Re: Qlikview converts date to nvarchar when calling SQL procedure

Sun, 06 Sep 2015 08:28:30 GMT

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?

Re: Qlikview converts date to nvarchar when calling SQL procedure

hic — Sun, 06 Sep 2015 11:31:02 GMT

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

Re: Qlikview converts date to nvarchar when calling SQL procedure

Mon, 07 Sep 2015 02:42:41 GMT

Thanks guys for this. I really appreciate the help.

Joel