SQL Query not displaying all records

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

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Query not displaying all records

Author

Topic

rajender
Starting Member

13 Posts

Posted - 2005-05-19 : 03:35:06

Hi Team

I am facing a strange problem with following queries , all the queries are for same purpose with different syntax

Select * from tbDatVerDet Where DatVerDetDat between '5/17/2005' and '5/19/2005' order by DatVerDetVerCod

Select * from tbDatVerDet Where Convert(Datetime,DatVerDetDat,110) between Convert(datetime,'5/17/2005',110) and convert(Datetime,'5/19/2005',110) order by DatVerDetVerCod

Select * from tbDatVerDet Where Convert(Datetime,DatVerDetDat,110)>=convert(datetime,'5/17/2005',110) and Convert(Datetime,DatVerDetDat,110)<=convert(Datetime,'5/19/2005',110) order by DatVerDetVerCod

Now the problem is all of these queries not fetching records for upper date i.e '5/19/2005' though the records are there. My point is what ever be the upper date in the query it displays/fetches the records for upper(date)-1 date. I never faced this problem before. I changed the datatype to datetime as well as smalldatetime, but it not worked.

What could be the reason.

Thanks
Rajender Kr.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-19 : 03:49:12

Thats because convert(Datetime,'5/19/2005',110) = 2005-05-19 00:00:00.000 which is midnight and so your query will only select upto and including the 2005-05-18

You could add another day to your upper most date
DATEADD(dd,1,CONVERT(datetime,'5/19/2005',110))

Andy

Beauty is in the eyes of the beerholder

rajender
Starting Member

13 Posts

Posted - 2005-05-19 : 08:35:41

Thanks Andy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources