Nisha (1/4/2008)
Ok.. Here's my query:SELECT * FROM [MyDateTable]
WHERE StartDate >= CONVERT(DATETIME, '02/01/2007')
AND StartDate <= CONVERT(DATETIME, '01/04/2008')
This works fine, Jeff! Which is why I was saying, I used Matt's ''don't convert your date field to string'' and I used your ' operators instead of the BETWEEN'.
Do you think I've gotten the point now? 🙂
Yes, except that if there's a row with a start time of midnight on the 4th you will get it, but you won't get any other records for the 4th. (eg 2008/01/04 00:01, 2008/01/04 07:51, ...)
If you need all the rows that have startdates on the 4th, then use
StartDate < CONVERT(DATETIME, '01/05/2008')
If you don't need anything from the 4th, then use
StartDate < CONVERT(DATETIME, '01/04/2008')
Datetimes are date and time, and you need to take that into account with your queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability