topic use date as a variable ? in QlikView

use date as a variable ?

Fri, 05 Mar 2010 12:31:19 GMT

i have generate two variable of date like that .. i think is a variable


LOAD
date(date#(20050101,'YYYYMMDD')+recno(),'DD/MM/YYYY') as "Date_start"
AUTOGENERATE today() - MakeDate(2005, 1, 1) + 1;

LOAD
date(date#(20050101,'YYYYMMDD')+recno(),'DD/MM/YYYY') as "Date_end"
AUTOGENERATE today() - MakeDate(2005, 1, 1) + 1;

and after i select them and load the script must execute a sql query like that :


and i.trndate
 between convert(datetime,'$Date_start',103)
 and convert(datetime,'$Date_end',103)

i receive an error :

SQL error: Syntax error converting datetime from character string.
Script line: .... and i.trndate between convert(datetime,'$Date_start',103) and convert(datetime,'$Date_end',103)

any tips ?

use date as a variable ?

sparur — Fri, 05 Mar 2010 12:37:39 GMT

Date_start and Date_end in your script aren't variables.

You create a 2 fields 🙂

for create a variable try that after your load script;

LET vStartDate = peek('Date_start', 0) ;

LET vEndDate = peek('Date_end', 0) ;

and than use this variable in your later script. But I'm not sure about sql function convert().

use date as a variable ?

Fri, 05 Mar 2010 15:13:50 GMT

and in the sql query who i can use them ?

with $ or in '' ??

use date as a variable ?

rwunderlich — Fri, 05 Mar 2010 18:23:24 GMT

convert(datetime,'$(vStartDate)',103)

The expression reference syntax is:

$(varname)

Don't forget the parens.

-Rob