Question about date format

[#1]

I want my QlikView app to use the dates as I am using in the SQL.

My code looks like this:

LOAD column A;

SELECT * FROM table

WHERE Date BETWEEN today & tomorrow

I tried several different ways, but it doesn't work for me. Does anyone know how to do it?

I also tried to define a variable - vDate

LOAD column A;

SELECT * FROM table

WHERE Date BETWEEN '$(vDate)' & '$Date(Num(vDate) + 1)'

Thank you!

[vgutkovsky]

I think SQL BETWEEN syntax requires "and" not '&'

[#3]

Oh, sorry. i used "and" not "&". But it is not working for me.

LOAD column A;

SELECT * FROM table

WHERE Date BETWEEN today AND tomorrow

LOAD column A;

SELECT * FROM table

WHERE Date BETWEEN '$(vDate)' AND '$Date(Num(vDate) + 1)'

[vgutkovsky]

Your end date formatting seems to be wrong. I would try something like this:

LET vStartDate = ...

LET vEndDate = ...

WHERE Date BETWEEN '$(vStartDate)' and '$(vEndDate)'

Make sure the date formatting in the variables matches how your dates are formatted in the DB.

Regards,
Vlad

[#5]

hello, try this to avoid problems with formating which I think might be your problem now

vToday should be for example = 17/05/2012

vTomorrow should be for example = 18/05/2012

Where CONVERT(nvarchar(6),Date,103) >= '$(vToday)' and CONVERT(nvarchar(6),Date,103) >= '$(vTomorrow);

try this, I use this to run some scripts and it works very well.

Best.

[#6]

Thank you for the help guys!

QlikView seems very picky.

I tried this:

Let vToday = Date(Today(),'MM/DD/YYYY');

Let vTomorrow = Date(Num(Today()) + 1,'MM/DD/YYYY');

LOAD column A;

SELECT * FROM table

where Date("DATE",'MM/DD/YYYY') between '$(vToday)' and '$(vTomorrow)'

I am confused why it's still not working.

[chematos]

Try this:

Let vToday = MakeDate(Today());

Let vTomorrow = MakeDate((Today() + 1));

LOAD column A;

SELECT * FROM table

where Date(Floor("DATE")) between '$(vToday)' and '$(vTomorrow)'

[#8]

HI Jose,

This method does not work. the vToday value would be "1/1/41046".

G

[chematos · Accepted solution]

How do you have these variables on your main sheet??

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';