Thursday, January 28, 2016

I'm not going to try to convince you in English not to use BETWEEN with dates. Run the code below to see why it's not a good idea. (Yes, I know there are new date types - this post isn't for you.)

-- Never use BETWEEN with dates. Just use >= and < . -- Drop temp table.
if object_id('tempdb..#MyDates') is not null drop table #MyDates
-- Create a table of edge-case dates to demonstrate the issue.
create table #MyDates ( MyDate datetime not null)
insert #MyDates (MyDate) values ('2015-10-31 23:59:59.997') -- October
insert #MyDates (MyDate) values ('2015-11-01 00:00:00.000') -- November
insert #MyDates (MyDate) values ('2015-11-01 00:00:00.003') -- November
insert #MyDates (MyDate) values ('2015-11-29 23:59:59.997') -- November
insert #MyDates (MyDate) values ('2015-11-30 00:00:00.000') -- November
insert #MyDates (MyDate) values ('2015-11-30 00:00:00.003') -- November
insert #MyDates (MyDate) values ('2015-11-30 23:59:59.997') -- November
insert #MyDates (MyDate) values ('2015-12-01 00:00:00.000') -- December
-- (1) Now let's get all the November dates using BETWEEN. -- We want to get 6 rows back, but this returns 4! Notice '2015-11-30 00:00:00.003' and -- '2015-11-30 23:59:59.997' are missing from the results.
select MyDate as '(1)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30' order by MyDate -- This is WRONG - do not use! -- (2) Okay, that didn't work, so let's try extending out an extra day. -- We want to get 6 rows back, but this returns 7! Notice '2015-12-01 00:00:00.000' is -- included in the results, but we only want November.
select MyDate as '(2)' from #MyDates where MyDate between '2015-11-01' and '2015-12-01' order by MyDate -- This is WRONG - do not use! -- (3) Hmmm, let't try going up to the last second of the last day of November. -- We want to get 6 rows back, but this returns 5! Notice '2015-11-30 23:59:59.997' -- is missing from the results, because it occurs *inside* that last second of November.
select MyDate as '(3)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30 23:59:59' order by MyDate -- This is WRONG - do not use! -- (4) So how *does* one use BETWEEN with dates correctly? This is the only way. -- (Why 997? Google for "sql server datetime 997" for more info.)
select MyDate as '(4)' from #MyDates where MyDate between '2015-11-01' and '2015-11-30 23:59:59.997' order by MyDate -- This is CORRECT! -- (5) But who wants to type all that out? Instead of using BETWEEN, do it this way.
select MyDate as '(5)' from #MyDates where MyDate >= '2015-11-01' and MyDate < '2015-12-01' order by MyDate -- This is CORRECT! -- Drop temp table.
if object_id('tempdb..#MyDates') is not null drop table #MyDates

Post a Comment

Before making the move to SQL Server, Larry specialized in Windows programming in C/Win32, C++/MFC, and C#/.NET. His blog focuses on query tuning, database optimization, and system performance, and features handy scripts, practical tips, and occasional dispatches from the many dark corners of SQL Server.