0

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...

  • For debugging purposes, you could add `DATEADD(millisecond,-3,ISNULL(@datefrom, @searchscope))` and `DATEADD(millisecond,-3,DATEADD(day,1,ISNULL(@dateto, GETDATE())))` to your SELECT output list and show them to us. – Heinzi Nov 18 '09 at 17:34
  • 1
    Why do the items booked in the final three milliseconds of 01 Jan count towards 02 Jan? – Cade Roux Nov 18 '09 at 17:44
  • And why is this vital for accounting purposes? – Heinzi Nov 18 '09 at 17:46
  • Cade, SQL Server 2005 can't get more precise than that. If it really happened at 23:59:59.998, it gets rounded down to .997; if it happened at 999, it gets rounded up to the next day. But using this "subtract milliseconds" trick is for the birds anyway; see https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx – Aaron Bertrand Nov 18 '09 at 17:49
  • @Aaron - right - the date in SQL Server is THE date. Whether the real date is different or not makes no difference. If it does make a difference and they need more precision, then they shouldn't be using datetime in the first place. – Cade Roux Nov 18 '09 at 18:02
  • @Cade Roux - Re: Final 3 milliseconds. You know... I'm not entirely sure. It used to settle from midnight to midnight but I think we found our bank's credit card transaction settling software "wobbled" so it has a similar drift. There was some reason for it. –  Nov 19 '09 at 10:00

2 Answers2

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))
Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Yup, see http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server – Mark Ransom Nov 18 '09 at 17:35
  • If @dateto is non-NULL (as One Monkey claims), the value of GETDATE() should be irrelevant, right? – Heinzi Nov 18 '09 at 17:36
  • @Heinzi - I suspect all the variables need their dates trimmed to date part only, and then the milliseconds stuff can be dropped too. – Cade Roux Nov 18 '09 at 17:38
  • 1
    Also see : https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx – Aaron Bertrand Nov 18 '09 at 17:46
  • @Aaron - great summary article. I just wish there was an automatic DATEBETWEEN (time parts ignored) or OPENUPPERDATEBETWEEN (time parts ignored, open endpoint at the upper end of the interval) which handled all that - it would prevent people lured into the temptation of using BETWEEN and all the other nonsense. – Cade Roux Nov 18 '09 at 18:05
  • I asked for an equivalent of TRUNC_DATE here, which would make the time portion easier to ignore: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=483913 ... it's not always an option to just not use DATETIME, because the time portion can be relevant for some reports, but not for others. – Aaron Bertrand Nov 18 '09 at 19:43
1

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.

Mark Ransom
  • 299,747
  • 42
  • 398
  • 622