Why does SQL Server 2005 miss a within range DATETIME value

Question

Can anybody tell me why my Database selection queries asking for an orderid

WHERE [order].dateplaced >= DATEADD(millisecond,-3,ISNULL(@datefrom, @searchscope))
AND   [order].dateplaced < DATEADD(millisecond,-3,DATEADD(day,1,ISNULL(@dateto, GETDATE())))

Is missing out an order over three and a half minutes before midnight on the @dateto when @datefrom and @dateto are both set to the same date?

I've tried altering the query so the initial DATEADD adds a second to both datetimes instead of taking away 3 milliseconds from both and it continues to not pull the order.

For reference the exact datetime of dateplaced is: 2009-01-20 23:56:17.933

Am I being dumb?

EDIT: I remember now why the three milliseconds thing came into play. It was because our accounts work on whole months and if you wanted a month's worth of reports you could set it from, for example, 01-Jan to 01-Feb and that would include everything up to midnight on 01 Feb. (Incidentally is that exclusive or inclusive?) However people were too dumb to actually set the date range to this they would set it from 01-Jan to 31-Jan and miss a day (don't ask me).

As I knew that SQL Server worked in resolutions of 3 milliseconds I first of all made a request for 31-Jan go until 11:59:59.997 on 31-Jan whereas 01-Feb would still go from midnight. However to compensate then on the date "from" I had to drop three milliseconds so nothing could slip through the cracks. I just presumed that SQL Server would be able to handle that. It's probably missing bits out of those reports now so I shall have to go and look those up.

Although the top-voted solution below works for all practical purposes (our bank's credit card settling software still randomly puts transactions from either side of midnight on the "wrong" side as far as our system is concerned) it still doesn't answer the question of why a transaction with a good three and a half minutes grace fails to get captured. I appreciate that just losing the time will work most of the time but the nature of our business means that on certain dates we have actual time periods of around twenty minutes where greater resolution and precision handling would be handy.

For the curious we sell concert tickets in the UK and on days where we have, for example, the Reading or V Festival going on sale we shift a couple of thousand tickets in the twenty minutes after on sale and the rest of the day have a normal amount of sales for other stuff. Those twenty minute periods become the target of much reporting and dissection as the load balancer isn't always perfect and weird record glitches do crop up. So being able to dice records down to stretches of seconds would be handy. My confidence in the software is a bit shaken by this so an actual answer would be handy.

However for the time being, the particular thing I'm doing is fine with the top-voted solution below...

Answer 1

GETDATE() has a time portion which you are not trimming off (you might want to trim the time part off of the other variables too). I don't know why you're messing around with milliseconds either. I know that 3 milliseconds is the smallest resolution, but I typically have never had to use it to work around range endpoints.

DECLARE @today AS DATETIME
SET @today = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

SET @datefrom = DATEADD(dd, 0, DATEDIFF(dd, 0, @datefrom))
SET @searchscope = DATEADD(dd, 0, DATEDIFF(dd, 0, @searchscope))
SET @dateto = DATEADD(dd, 0, DATEDIFF(dd, 0, @dateto))

SELECT *
FROM [order]
WHERE [order].dateplaced >= ISNULL(@datefrom, @searchscope)
AND   [order].dateplaced < DATEADD(day, 1, ISNULL(@dateto, @today))

Answer 2

This works for me in SQL 2000 and 2008:

declare @datefrom datetime
declare @dateto datetime
set @datefrom='2009-01-20'
set @dateto='2009-01-20'
select case when '2009-01-20 23:56:17.933' >= DATEADD(millisecond,-3,@datefrom) then '>=' else 'NOT >=' end
select case when '2009-01-20 23:56:17.933' < DATEADD(millisecond,-3,DATEADD(day,1,@dateto)) then '<' else 'NOT <' end

There might be more to the problem than you're telling us.