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

getting stuck in SQL WHERE = Today when date is YYYYMMDD

Wed, 12 Sep 2012 10:13:26 GMT

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

DATEADD(day, 0, CURRENT_TIMESTAMP);

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!

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

Jason_Michaelides — Wed, 12 Sep 2012 10:18:42 GMT

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

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

Wed, 12 Sep 2012 10:23:37 GMT

[...]
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

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

Jason_Michaelides — Wed, 12 Sep 2012 10:26:28 GMT

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.

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

Wed, 12 Sep 2012 10:28:29 GMT

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

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

Jason_Michaelides — Wed, 12 Sep 2012 10:29:35 GMT

Pleasure.

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

sudeep_d — Thu, 27 Sep 2012 11:44:11 GMT

hii jason,

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

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

Jason_Michaelides — Thu, 27 Sep 2012 11:50:07 GMT

Just format Today() to match your format:

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

Hope this helps,

Jason

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

sudeep_d — Thu, 27 Sep 2012 12:04:57 GMT

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

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

Thu, 27 Sep 2012 12:08:37 GMT

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'

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

sudeep_d — Thu, 27 Sep 2012 12:12:29 GMT

date# equivalent function in sql wud solve my query..i guess

thanks

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

Jason_Michaelides — Thu, 27 Sep 2012 12:14:38 GMT

Yep - and there is the QV equivalent. Have you looked in the F1 help?

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

LOAD.....

WHERE Date(Date#(MyField,'DD-MMM-YY'),'DD-MMM-YY') = '$(vToday)';

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

sudeep_d — Thu, 27 Sep 2012 12:41:57 GMT

hi jason,

i am fetching data from SAP which uses SQl selectstatement before load hence i need sql function to change the data in date format.

eg:

a100:

load

myfield,

z_z4;

sql select myfield z_z4 from table1;

script looks something like this .. so what should be my where statement after table1??

date# wont work because it's not a sql function

thanks for concern

sudeep