Don't Use BETWEEN with DATETIME

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