SQL where date format gives wrong results

[#1]

Hello there,

With Qlikview and SQL 2008 a like to get only this month results not last month

When I do a load from SQL on all data en then a where statement in qlikview I get as reuslt all data from this month here is the code

partij:
LET dezemaand = num(month(today()));
load Datum,
 KwekerID,
 ArtikelID
 where month(Datum) = $(dezemaand);
 select * FROM "Match-Online Test".dbo.Partij;

Then the result is all line with date stamp for this month (september)
But now I only want to get this date from the SQL DB en this is my code now

partij:
LET dezemaand = num(month(today()));
LOAD * ;
SELECT Datum,
 KwekerID,
 ArtikelID
FROM "Match-Online Test".dbo.Partij where month(Datum) = $(dezemaand);

And now the are 2 extra days in the line that match this month. Those are 30 and 31 of augustus.

Anyone knows this problem?

[#2]

The only thing I'm not sure about is how the load statement in QlikView is working, certainly in SQL you would need to check the year number as well as the month, otherwise you will get rows for August, but for every year (I'm also not too sure that a comparison to a QVW variable in the SQL statement will work), so your SQL WHERE clause should look like:

WHERE Month(Datum) = Month(GetDate()) AND Year(Datum) = Year(GetDate())

For info, GetDate() in SQL will return today's date.

[blaise]

If you store num(date(today())) as a variable and use it in a where statement in a sql select, you won't receive dates from today. Qv and SQL server treats those 'number dates' differently. But, if you do a num(date(today()))-2 you will get the correct dates from the sql statement.

[#4]

why don't you check your sql query directly (not thru qlikview) like this

SELECT Datum, KwekerID, ArtikelIDFROM "Match-Online Test".dbo.Partij where month(Datum) =9;

If this is giving correct result, then there is something to worry in qlikview. Otherwise it shuld be the problem with database/data

[#5 · Accepted solution]

I got it,

in the statement where Month(Datum) = Month(Getdate())

still give 2 days from last month but when I do

where Month(Datum-2) = Month(Getdate()) it works fine.

Is this strange behaver from qlikview or SQL ???

Now created this one

let timeformaterror=2;
let thismonth = num(month(today());
sql select Datum
FROM "Match-Online Test".dboPartij
 where Month(Datum- $(timeformaterror) = $(thismonth);

And this way when this problems doens't occure anymore we only have to set timeformaterror to 0

[#6]

Hai Richy

when I use number 9 still got 2 days from last month
Also when i Use Getdate statements still 2 days from last month

But with my work around I go on to the next challenge

[#7]

What you have done cannot be a solution.

Consider a row having date 01Sep2009. Then by giving -2, the date becomes 30Aug2009, and it will get filtered out by your where clause.

And how did u check the query giving '=9'. Is it from qlikview or from your sql query tool. I wanted you to check from the query tool. If its still giving you wrong results, then atleast keep in mind that there is something wrong with your data.

[blaise]

Let vToday = num(today());
SQL SELECT DateField from database1.dbo."Company$Invoice" where DateField = $(vToday);

will not fetch any values.

Let vToday = num(today())-2;
SQL SELECT DateField from database1.dbo."Company$Invoice" where DateField = $(vToday);

Will return a dateField with 2009-09-16 as only value.

[#9]

Hoi Richy,

if done a sql query. (direct on the database with query tools (that is also fun )

with the same code and the result is the same.

Month september and 2 days from august.

So where is this problem coming from? This is an windows 2008 with sql 2008.

Qlikview is on windows 2003 and version 9

[#10]

Since you have queried it directly, and result is coming wrong, we don't have to relate it with qlikview. Now u gotta find what is wrong in your database. Try the same query using you query tool, but this time changing months.ie, first try with month(date)= 8, then 7 like that. See if same problem exists.