Simple Query has small bug - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Simple Query has small bug

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-04 : 13:03:45

HI I am running this query in the C# code. It's displaying a dialog box with a from and to date and also a from and to for Docnumber.
The default is blanks for both. the date refers to docnumber date.
I have noticed something strange. I enter 2 document numbers both from Aug 2. If I enter dates Aug 1 -3 along with the document numbers,
it returns the documents.
also for Aug 2-3.
however if i just enter Aug 2 thru Aug 2. it returns nothing.

Seems strange is there an explanation?

"if (vm.DocNumFrom.HasValue && vm.DocNumTo.HasValue)
docQueryString += string.Format(@" AND ([DOCNUMBER]>={0} AND [DOCNUMBER]<={1}) ", vm.DocNumFrom, vm.DocNumTo);
else if (vm.DateFrom != null && vm.DateTo != null)
{"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-04 : 13:10:14

You have time portion attached to your date field?

consider:

declare @t table (dt smalldatetime)
insert @t values (getdate())
select * from @t where dt between '20110804' and '20110805'
select * from @t where dt between '20110804' and '20110804'

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-04 : 14:04:53

quote:
Originally posted by russell

You have time portion attached to your date field?

consider:
declare @t table (dt smalldatetime)
insert @t values (getdate())
select * from @t where dt between '20110804' and '20110805'
select * from @t where dt between '20110804' and '20110804'

thanks. Sorry i left out the line that does the dates comparison
the dates are coming from the dialog box entered by the user. sorry this line i left out this is the date test vm.dateto and vm.datefrom

docQueryString +=

string.Format(@" AND ([DWSTOREDATETIME] >=""{0}"" AND [DWSTOREDATETIME]<=""{1}"") ", String.Format("{0:yyyy-MM-dd}", vm.DateFrom), String.Format("{0:yyyy-MM-dd}", vm.DateTo));

}

?i think its defined here

DateTime

_dateTo;

public DateTime DateTo

{

get

{

return _dateTo;

}

set

{

_dateTo =

value;

RaisePropertyChanged(

"DateTo");

}

}

?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-04 : 14:06:30

What is the actual query passed to the db engine?

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-04 : 16:30:06

[Doctype] = 'PO' AND ([Dwstoredatetime])="2011-08-02" and [Dwstoredatetime] <="2011-08-02") and ([Docnumber] >=25748 and [Docnumber] <= 25749

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-04 : 16:35:51

That's only going to return rows where Dwstoredatetime is exactly 2011-08-02 00:00:00.000

If that column has times as well as dates it's unlikely anything will be at exactly midnight. If the column does have times, you need to change it to the form

AND Dwstoredatetime >= @StartDate AND Dwstoredatetime < @EndDate

and you need to ensure that @EndDate is the max of the range they want, probably using DATEADD(dd,1,@EndDate)

--
Gail Shaw
SQL Server MVP

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-04 : 16:50:00

The string literal '2011-08-02' is implicitly converted to the datetime value of 2011-08-02T00:00:00.000. What you are asking SQL Server to return for you are any rows where the Dwstoredatetime column value is >= 2011-08-02T00:00:00.000 AND <= 2011-08-02T00:00:00.000.

The BETWEEN operator will be converted to a >= AND <= comparison in the execution plan.

If the column Dwstoredatetime has a value of 2011-08-02T00:00:00.003 that row is not BETWEEN your two date parameters. Therefore, you will not get any rows that match.

This is one of the reason why it is not recommended to use BETWEEN with datetime data types. Especially where there is a time component to the column values. It is much better to use an open-interval range check instead - as in:

WHERE Dwstoredatetime >= '2011-08-02'
AND Dwstoredatetime < dateadd(1, day, '2011-08-02')

This will insure that you always get all dates requested for the selected end date parameter.

Jeff

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2011-08-04 : 16:59:04

thanks to you both. does it matter that i am running the query against a mysql?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-04 : 17:11:29

Yes. This is a SQL Server forum, we all assume if you post here you're using SQL Server.
The concept will probably apply, the syntax won't.

For MySQL help the MySQL forums at Oracle or dbforums.com are going to be a lot better.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources