getting stuck in SQL WHERE = Today when date is YYYYMMDD

[#1]

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!

[Jason_Michaelides · Accepted solution]

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

[#3]

[...]

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]

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.

[#5]

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

[Jason_Michaelides]

Pleasure.

[sudeep_d]

hii jason,

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

[Jason_Michaelides]

Just format Today() to match your format:

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

Hope this helps,

Jason

[sudeep_d]

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

[#10]

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'