1

I'm trying to do some DateTime logic in SQL:

SET @DayEnd = DATEADD(MILLISECOND, -1, @BeginNextDay)

If, for instance, @BeginNextDay is '2019-02-04 00:00:00.000' and I'm performing the code above, I would expect @DayEnd to be '2019-02-03 29:59:59:999'. Unfortunately this isn't the case, the result is '2019-02-04 00:00:00.000'.

When I'm subtracting 12 milliseconds, the result is '2019-02-03 23:59:59.987'.

When I'm substracting 10 milliseconds, the result is '2019-02-03 23:59:59.990' as expected.

Can somebody explain to me what SQL is doing that gives me this (for me) unexpected result?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
TimothyHeyden
  • 1,018
  • 10
  • 27
  • 1
    The old `DateTime` is accurate to only 3 milliseconds. That's why it works for 10ms, but not for 1ms. – Tim Biegeleisen Apr 12 '19 at 10:55
  • I think it depends on the implementation of the timestamp data type. I know of systems using double as datetime: integer part is date, the rest is time. That approach lacks precision needed for exact milliseconds... – Usagi Miyamoto Apr 12 '19 at 10:58
  • 1
    First, don't do this. Second, if you want to see if a time falls in a date just use the `date` type, eg `WHERE cast(TransactionTime as date)=@someDateTypedParameter`. SQL Server is smart enough to convert that cast into a range search equivalent to `TransactionTime > @someDate AND TransactionTime < dateadd(day,1,@someDate)` – Panagiotis Kanavos Apr 12 '19 at 11:11
  • Maybe [this](https://stackoverflow.com/questions/47683319/why-does-datediff-treat-2359-59-999-as-the-next-day) can shed some light on this – GuidoG Apr 12 '19 at 11:27

1 Answers1

2

Don't do it! Just use:

SET @DayEnd = @BeginNextDay;  -- if you even need this

And change your logic to be:

WHERE datecol < @DayEnd

rather than:

WHERE datecol <= @DayEnd  -- or equivalently using BETWEEN

Don't fiddle with milliseconds to define time periods. Use >= and < to define the period.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786