Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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_Michaelides
Luminary Alumni
Luminary Alumni

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

View solution in original post

12 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

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
Author

[...]

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_Michaelides
Luminary Alumni
Luminary Alumni

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
Author

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Pleasure.

sudeep_d
Partner - Creator
Partner - Creator

hii jason,

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

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Just format Today() to match your format:

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

Hope this helps,

Jason


sudeep_d
Partner - Creator
Partner - Creator

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
Author

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'