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?

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
user109039
  • 21
  • 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 –  Oct 26 '16 at 15:56

2 Answers2

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.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
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.

ypercubeᵀᴹ
  • 97,895
  • 13
  • 214
  • 305
Shooter McGavin
  • 857
  • 4
  • 14
  • 1
    WHERE CAST(achieved_date AS DATE) BETWEEN '20160901' AND '20160930' would work just as well. – Andriy M Oct 27 '16 at 00: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 Oct 27 '16 at 00: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 Oct 27 '16 at 00: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ᵀᴹ Oct 30 '16 at 01:11
  • See Cast to date is sargable but is it a good idea? – ypercubeᵀᴹ Oct 30 '16 at 01:16