2

I have a column titled achieved_date. In my query most of the values for that column are 2016-09-30 23:59:59.997.

I then have a where clause that reads as follows:

WHERE achieved_date between '20160901' and '20160930'

The records with the 2016-09-30 23:59:59.997 value are not being included in my results when they should be. I mean the transaction was recorded on the 30th of September.

Any ideas if this is a odd datatype issue?

Improve this question
1
  • 1
    '20160930' is the same a 2016-09-30 00:00:00 so yes, that won't include any rows that have a time after midnight
    – user1822
    Commented Oct 26, 2016 at 15:56

2 Answers 2

Reset to default
7

Stop using BETWEEN for date range queries; it's ambiguous and brittle. WHERE COL >= '20160901' and COL < '20161001' is so much easier to construct and isn't vulnerable to unexpected changes in type / precision / rounding.

See this post and this post.

Improve this answer
1

I have to agree with Aaron Bertrand here. Using 'between' in this situation introduces a certain amount of ambiguity when reading your code. Using more specific operators =,>=,<= etc, will provide more readable code and reliable results.
I also agree with SQLDataInTraining in that the implicit conversions involved are actually causing your results to not return the expected records. Another option though is to cast the DB value as opposed to the literal dates values.

So instead of

WHERE achieved_date > CAST('20160901' AS DATE) 
 AND achieved_date <= CAST('20160930' AS DATE)

try this

WHERE CAST(achieved_date AS DATE) >= '20160901' 
  AND CAST(achieved_date AS DATE) <= '20160930'

This is of course assuming you want all records from both 20160901 and 20160930. If not, adjust the operators accordingly.

Improve this answer
5
  • 1
    WHERE CAST(achieved_date AS DATE) BETWEEN '20160901' AND '20160930' would work just as well.
    – Andriy M
    Commented Oct 27, 2016 at 0:03
  • It should, yes. However, to Aaron's point, readability and control are sacrificed when using between. Not saying it won't work. Just saying there are better ways.
    – Shooter McGavin
    Commented Oct 27, 2016 at 0:07
  • 2
    Well, the best way is to use an open-ended range, as Aaron has suggested. The way you've modified the OP's original condition, you are still using the same pair of conditional operators, >= and <=, for which BETWEEN is a shorthand. It makes even more sense to use it in your case because the term that's being matched against the range is now longer than before (CAST(achieved_date AS DATE) vs just achieved_date previously) and repeating it makes the condition even longer.
    – Andriy M
    Commented Oct 27, 2016 at 0:15
  • The problem with BETWEEN is not readability. It's that you have to do different casts depending on the cut-off (is it at 00:00 or at 12:00, etc) or weird date math. Also if you do cast a column as in your code, you may end with an execution plan that doesn't use indexes (you lose sargability), depending on version. Another issue is that DATE is not a valid type in older versions. Using >= and <, as in Aaron's answer, none of these problems exist.
    – ypercubeᵀᴹ
    Commented Oct 30, 2016 at 1:11
  • See Cast to date is sargable but is it a good idea?
    – ypercubeᵀᴹ
    Commented Oct 30, 2016 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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