Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

getting stuck in SQL WHERE = Today when date is YYYYMMDD

Hey,

I'm trying to retrieve data, limiting it in SQL according to Today’s date.

The problem is that the date is stored in YYYYMMDD format, therefore not in proper SQL format

Things like

WHERE Date = GetDate()

Or


DATEADD(day, 0, CURRENT_TIMESTAMP); 

Or


WHERE Date = CONVERT(CHAR(10),GETDATE(),112);

WHERE Date = CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]

If (to avoid confusion) we now define the field I need to look in for today’s date as MyField, how would the script look like?

Load *;

SQL SELECT

Field1, Field2, MyField from Server.table WHERE MyField = (?);

Thank you!

1 Solution

Accepted Solutions
jason_michaelid
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

In the QV script, before the SQL load put:

LET vToday = Date(Today(),'YYYYMMDD');

Then, in your SQL query:

LOAD

     *

;

SQL SELECT

     Field1,

     Field2,

     Field3,

     etc

FROM Table

WHERE MyField = '$(vToday)';

Hope this helps,

Jason

12 Replies
jason_michaelid
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

In the QV script, before the SQL load put:

LET vToday = Date(Today(),'YYYYMMDD');

Then, in your SQL query:

LOAD

     *

;

SQL SELECT

     Field1,

     Field2,

     Field3,

     etc

FROM Table

WHERE MyField = '$(vToday)';

Hope this helps,

Jason

Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

[...]

WHERE MyField = '$(vToday)';

Hope this helps,

Jason

Perfect, fast, awesome!

Can I award you somehow 20 points?

Just a question:

Is this '$(vToday)' a QV-modded SQL language? I'm asking this becaus I never stumbled in something similar to the solution you provided, when I was browsing in many different SQL helps

jason_michaelid
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

Yes, it's QV code - you will see and hear about "dollar-expansions" as you use QV more and more.  The $ evaluates the expression (in this case a simple variable) and the single-quotes mean your SQL won't break.

Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

there you go 15 points , thank you very much for your further input!

jason_michaelid
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

Pleasure.

sudeep_d
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

hii jason,

what if myfield has date in text format(e.g 23-sep-12) which is not in date format??

jason_michaelid
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

Just format Today() to match your format:

LET vToday = Date(Today(),'DD-MMM-YY');

Hope this helps,

Jason


sudeep_d
Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

since myfield has data in text format so system is not able to compare the variable with a text..

i hope u have figured out the issue

thanks

Not applicable

Re: getting stuck in SQL WHERE = Today when date is YYYYMMDD

I think this string which you have at teh top of every QV script

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

does the trick and let QV recognises your "Sep" when you input 'MMM'