topic SQL where date format gives wrong results in QlikView

SQL where date format gives wrong results

Wed, 16 Sep 2009 18:10:59 GMT

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?

SQL where date format gives wrong results

Wed, 16 Sep 2009 18:43:01 GMT

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.

SQL where date format gives wrong results

blaise — Wed, 16 Sep 2009 19:18:39 GMT

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.

SQL where date format gives wrong results

Wed, 16 Sep 2009 19:40:55 GMT

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

SQL where date format gives wrong results

Wed, 16 Sep 2009 19:53:29 GMT

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

SQL where date format gives wrong results

Wed, 16 Sep 2009 19:58:32 GMT

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

SQL where date format gives wrong results

Wed, 16 Sep 2009 20:17:23 GMT

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.

SQL where date format gives wrong results

blaise — Wed, 16 Sep 2009 20:30:51 GMT


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.

SQL where date format gives wrong results

Wed, 16 Sep 2009 20:52:13 GMT

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

SQL where date format gives wrong results

Wed, 16 Sep 2009 20:57:30 GMT

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.

SQL where date format gives wrong results

Wed, 16 Sep 2009 21:01:53 GMT

Done that already,

The database is from april this year. and all months got the same 2 days in the last month.

I have contacted the software house of the database if the know what is wrong.

But when I read the reply of Blaise He also got the same issue. Or is my English reading not so good yet. (I am a Dutch Guy)

SQL where date format gives wrong results

Wed, 16 Sep 2009 21:28:30 GMT

Now i wonder, whether you people consider last two days of a month as next month.

Anyway you try one more thing:

SELECT Datum, KwekerID, ArtikelID FROM "Match-Online Test".dbo.Partij where

date('2009-08-01') <= date(Datum) AND date(Datum)<=date('2009-08-31')

SQL where date format gives wrong results

blaise — Thu, 17 Sep 2009 04:01:45 GMT

If you use Toad or some other query tool (like mssql management studio express) you should NOT have a problem with "where month(Datum)=8. This should fetch all dates within August. As Ricky is saying there seems to be a problem in your db (ie not Qv related).

try this query ;


select distinct(Datum) from "Match-Online Test".dbo.Partij where month(Datum)=8

This will list all the fetched dates within month(Datum)=8. Also, have a look at the Datum field, is it a datetime format or something other?

SQL where date format gives wrong results

prieper — Thu, 17 Sep 2009 22:02:30 GMT

Hi,

can recall that this is a very old bug, where MS starts to count the days as from Dec 31st, 1899 and has further "forgotten" to calculate that 1900 was a leapyear. QV counts the Jan 1st, 1900 as day 1. In your example you hand over a numeric value into another system, which has a different time-base. You may deduct the 2 days or hand over the date in a format, which is understood to be interpreted correctly, like:

LET sDate = chr(39) & date(today() , 'MM/DD/YYYY') & chr(39);

and put this into the SQL-statement.

Peter

SQL where date format gives wrong results

Fri, 18 Sep 2009 12:53:50 GMT

Hello Blaise,

<pre>select distinct(Datum) from "Match-Online Test".dbo.Partij where month(Datum)=8

done that Query to the SQL Still got the same results.

Got all days to august 29 and 30 and 31 of July (or was it June still can't get those 2 rigth)

But in the next reply there seems to be an answer.