USE tempdb;
SET NOCOUNT ON;
GO

-- create a simple orders table
CREATE TABLE dbo.Orders
(
  OrderID   int IDENTITY(1,1) PRIMARY KEY,
  OrderDate datetime2(7)
);
GO

-- generate all the days in February at midnight
WITH x AS (SELECT n = 0 UNION ALL SELECT n + 1 FROM x WHERE n < 28)
SELECT DATEADD(DAY, n, '20200201') FROM x;

-- insert an order for each day in February
WITH x AS (SELECT n = 0 UNION ALL SELECT n + 1 FROM x WHERE n < 28)
INSERT dbo.Orders(OrderDate) 
SELECT DATEADD(DAY, n, '20200201') FROM x;

-- validate there are 29 rows, all in February:
SELECT DATENAME(MONTH, OrderDate), YEAR(OrderDate), COUNT(*)
FROM dbo.Orders
GROUP BY DATENAME(MONTH, OrderDate), YEAR(OrderDate); 

-- then add a few edge cases:
INSERT dbo.Orders(OrderDate)
VALUES
  ('20200229 23:59:31'),
  ('20200229 23:59:59.9985000'),
  ('20200301');

-- check again, 31 orders in February, one in March:
SELECT DATENAME(MONTH, OrderDate), YEAR(OrderDate), COUNT(*)
FROM dbo.Orders
GROUP BY DATENAME(MONTH, OrderDate), YEAR(OrderDate); 
GO

-- now use various tricks to find the "end" of the month

-- EOMONTH
DECLARE @m datetime = '20200201'

SELECT 'EOMONTH', OrderCount = COUNT(*) 
FROM dbo.Orders
WHERE OrderDate BETWEEN @m AND EOMONTH(@m);
GO

-- subtract a minute with smalldatetime
DECLARE @s smalldatetime = '20200201';
DECLARE @e smalldatetime = DATEADD(MINUTE, -1, DATEADD(MONTH, 1, @s));

SELECT '1m smalldatetime', OrderCount = COUNT(*)
FROM dbo.Orders
WHERE OrderDate BETWEEN @s AND @e;
GO

-- common 3 millisecond trick
DECLARE @s datetime = '20200201';
DECLARE @e datetime = DATEADD(MILLISECOND, -3, DATEADD(MONTH, 1, @s));

SELECT '3ms datetime', OrderCount = COUNT(*) 
FROM dbo.Orders
WHERE OrderDate BETWEEN @s AND @e;
GO

-- common 3 millisecond trick with smalldatetime
DECLARE @s smalldatetime = '20200201';
DECLARE @e smalldatetime = DATEADD(MILLISECOND, -3, DATEADD(MONTH,1,@s));

SELECT '3ms smalldatetime', OrderCount = COUNT(*)
FROM dbo.Orders
WHERE OrderDate BETWEEN @s AND @e;
GO

-- common 3 millisecond trick with datetime2
DECLARE @s datetime2(7) = '20200201';
DECLARE @e datetime2(7) = DATEADD(MILLISECOND, -3, DATEADD(MONTH,1,@s));

SELECT '3ms datetime2', OrderCount = COUNT(*)
FROM dbo.Orders
WHERE OrderDate BETWEEN @s AND @e;
GO

-- Always use a half-open interval.
-- Easier to find beginning of next period than end of current period:
DECLARE @start date = '20200201';

SELECT 'open interval', OrderCount = COUNT(*)
  FROM dbo.Orders
 WHERE OrderDate >= @start
   AND OrderDate <  DATEADD(MONTH, 1, @start);
GO

-- clean up:
DROP TABLE dbo.Orders;
GO