topic Question about date format in QlikView

Question about date format

Thu, 17 May 2012 16:30:09 GMT

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!

Question about date format

vgutkovsky — Thu, 17 May 2012 16:45:15 GMT

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

Question about date format

Thu, 17 May 2012 16:50:25 GMT

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

Question about date format

vgutkovsky — Thu, 17 May 2012 16:55:49 GMT

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

Question about date format

Thu, 17 May 2012 17:57:01 GMT

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.

Re: Question about date format

Thu, 17 May 2012 19:14:53 GMT

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.

Question about date format

chematos — Thu, 17 May 2012 19:18:59 GMT

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

Re: Question about date format

Thu, 17 May 2012 19:29:00 GMT

HI Jose,

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

Question about date format

chematos — Thu, 17 May 2012 19:32:35 GMT

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