2

Would you consider

WHERE SomeDate <= '2018-01-01 23:59:59'

instead of

WHERE SomeDate < '2018-01-02'

When the intention is that SomeDate goes no further then 2018-01-01

a code smell and if yes then why

  • This is not a question of style, it's a difference in semantics. The two tests either do the same or they don't, depending on how high the resolution of your Date data type is. – Kilian Foth Jan 31 at 14:02
  • Added a clarification about the intention – axk Jan 31 at 14:03
  • Yes, just arguing with a collegue, my argument is that it is an unnecessary complication and a potential bug (though highly unlikely). His argument is that the first one is more obvious than the second one and that relaistically the bug won't hapen. – axk Jan 31 at 14:12
  • 3
    Have you (and your colleague) considered the existence of leap seconds? '2016-12-31 23:59:60' is a Date value that actually happened and it is part of the day '2016-31-12'. – Bart van Ingen Schenau Jan 31 at 14:47
  • Anecdotal: I find these are often paired with (or at least, the practice derived from) using a greater-than comparison for 'safety' against the underlying type. For example, if you wanted "3rd Jan or later", then SomeDate > '2018-01-02' works when SomeDate is a date, but can fail when SomeDate is a datetime, because 2018-01-02 is 2018-01-02 00:00:00, and so something like 2018-01-02 15:30 satisfies the condition. Of course, there's much better ways to do that (SomeDate >= '2018-01-03'), but I've found that to be the reason why 23:59:59s crop up in SQL sometimes. – Kai Jan 31 at 14:53
4

I would always use

SomeDate < '2018-01-02'
  1. Do you really want to worry about the resolution of the underlying datatype when you make the selection?

  2. It's neater

  3. It works with spans of time. 2018-01-01 + 1 day = 2018-01-02

  4. = 2018-01-01 23:59:59 happens 1 second earlier than < 2018-01-02

  5. Leap seconds are a thing so you need <= 2016-12-31 23:59:60

I will add more reasons as I think of them. There are many.

  • 3
    How about leap seconds? Now it's <= ... 23:59:60 – bitsoflogic Jan 31 at 15:30
2

This depends on the resolution of your data type.

If all you can represent is seconds, then the two are equivalent, and there's no point in writing the more complicated expression. It's a smell because it gives the impression that whoever wrote this doesn't know how their date type works.

If you can represent milliseconds or even microseconds, then the first test is stricter than the second, so they are not interchangeable. Since it is much more likely that you want to compare against "midnight" rather than "less than 1s before midnight", in this case the first alternative is probably outright wrong.

Hence, the second alternative is probably preferable in most circumstances. If you really do have to check against a time immediately before midnight (perhaps you're creating a bomb timer in a superhero movie?), this is sufficiently unusual that you should add a comment explaining that yes, you actually do have to compare against this weird value.

2

The most reliable way is to always use '>=' when you need to define the beginning of a period, and '<' when you need to define the end of a period:

WHERE SomeDate >= '2019-01-01 00:00:00' AND SomeDate < '2019-02-01 00:00:00'

This approach allows to minimize the number of possible "surprises".

0

I wouldn’t say it is a code smell, I would say neither of your options say exactly what you mean. I would write that as:

WHERE cast(somedatetime as Date) <= '2018-01-01'

This says exactly what you want it to: when the date part of somedatetime, considered as a date, is less than or equal to this other date, include it in the results.

If somedatetime is supposed to just hold dates then change it to somedate and remove the cast.

I use the less-than-equal-to approach because it is clear that the final date is intentional.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.