tag:blogger.com,1999:blog-2328222207349876984.post5956072850406669944..comments2024-03-02T15:36:45.785-08:00Comments on SQL Tact: How BETWEEN is inclusiveUnknownnoreply@blogger.comBlogger5125tag:blogger.com,1999:blog-2328222207349876984.post-85567537802214490882016-12-29T13:25:10.798-08:002016-12-29T13:25:10.798-08:00This really depends if your column is a DATETIME r...This really depends if your column is a DATETIME rather than a DATE column. In the case of a DATETIME column the need to use CAST(DATECOLUMN as DATE) will make the query inclusive.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-12253901177477085912012-10-01T14:45:21.856-07:002012-10-01T14:45:21.856-07:00this has less to do with the BETWEEN operator and ...this has less to do with the BETWEEN operator and more to do with implicit conversion. Even though you typed BETWEEN 12/1/2011 and 12/31/2011 (which you meant to say if it happened during December of 2011 I care about it.) Your values were "implicitly" converted to two 4 byte integers representing the dateTIME values of BETWEEN 12/1/2011 00:00:00.000 AND 12/31/2011 00:00:00.000. Which means what you actually said was "if it happened in December of 2011 but not on the last day of the year I care about it."<br /><br />What you really meant to say was BETWEEN 12/1/2011 00:00:00.000 AND 12/31/2011 23:59:59.997 (note the MS, not explained here there are tons of articles covering 3ms cutoff in SQL)<br />or (less performant) MONTH(YourDateColumn) = 12 AND YEAR(YourDateColumn) = 2011.<br /><br />My advice to developers would not be to STOP using between for dates, but rather to make sure that if your column cares about time, your where clause should too. <br /><br />If I see a WHERE clause that says BETWEEN 12/1/2011 and 1/31/2011, I expect the column to never include any time other than 00:00:00.000 (maybe even constrained). <br /><br />So, as a T-SQL developer, whether you are using >= / <= or BETWEEN. If your data cares about the time, make sure your code does too.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-66455535774783756342012-10-01T04:31:39.576-07:002012-10-01T04:31:39.576-07:00Anonymous - that would of course also work but IMO...Anonymous - that would of course also work but IMO is unwieldy.whttps://www.blogger.com/profile/04313158818403554742noreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-83330019828836055962012-10-01T03:00:35.769-07:002012-10-01T03:00:35.769-07:00Or add a time to the second date in the between:
...Or add a time to the second date in the between:<br /><br />select * from datebetweentesting where testdate between '12/30/2011' and '12/31/2011 23:59:59.999'Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2328222207349876984.post-14149347432873917492012-10-01T01:10:38.847-07:002012-10-01T01:10:38.847-07:00But surely anything that happens after midnight on...But surely anything that happens after midnight on 31/12 is on the 1st Jan?Anonymoushttps://www.blogger.com/profile/13360264351879883586noreply@blogger.com