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]

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)

[#3]

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

[blaise]

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?

[prieper]

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

[#6]

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.