Would you consider
WHERE SomeDate <= '2018-01-01 23:59:59'
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
Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.Sign up to join this community
I would always use
SomeDate < '2018-01-02'
Do you really want to worry about the resolution of the underlying datatype when you make the selection?
It works with spans of time. 2018-01-01 + 1 day = 2018-01-02
= 2018-01-01 23:59:59 happens 1 second earlier than < 2018-01-02
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.
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.
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".
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.