Elapsed months logic with tests 2019-06-27

README.md

I needed to count the actual whole calendar months between two dates with no assumptions about the nature of anything between them. That means no knowledge of days in each month (which vary even before considering leap years), seconds in each day (which may vary with time zones or leap seconds), or even months in each year. Adding magic numbers, or worse, enumerating lots of Gregorian calendar eccentricities, are nasty code smells.

Unsatisfied with solutions like SQL Server's DATEDIFF() that naively counts datepart boundaries and Oracle's MONTHS_BETWEEN() that naively thinks every month is 31 days long, I checked StackOverflow and other usual resources, but nobody had a sane solution. Some authors suggested calendar tables (✋), and others suggested CLR functions (⚠️); many solutions mimicked the faulty assumptions of the Oracle and MS SQL Server functions named above, and even more had new bugs of their own.

So here's mine; the part you'll want is near the bottom, in the top level case ... end. It could use a little cleanup, and using those non-SARGable functions in a where clause will always be slow, but for the report I'm writing where this becomes a selected column, it works just fine for forward and backward intervals (positive and negative integers) and intervals that are less than a month in either direction that are reported as zero.

If you fork it and improve it, or just want me to add more tests, please let me know with a comment! I'd love to merge good changes.

months-elapsed.sql

declare @tests table ("start" datetime, "end" datetime, "expected" bigint not null);

insert into @tests ("start", "end", "expected")
values

-- exactly one month
('2019-01-01', '2019-02-01', 1),
('2019-01-28', '2019-02-28', 1),
('2018-12-01', '2019-01-01', 1),
('2018-12-31', '2019-01-31', 1),

-- exactly one month with times
('2019-01-01 01:23:45', '2019-02-01 01:23:45', 1),
('2019-01-28 01:23:45', '2019-02-28 01:23:45', 1),
('2018-12-01 00:00:01', '2019-01-01 00:00:01', 1),
('2018-12-31 23:59:59', '2019-01-31 23:59:59', 1),

-- less than one month
('2019-01-02', '2019-02-01', 0),
('2019-01-29', '2019-02-28', 0),
('2018-12-02', '2019-01-01', 0),
('2019-01-01', '2019-01-31', 0),
('2019-01-01', '2019-01-31', 0),
('2019-01-28', '2019-02-27', 0),
('2018-12-01', '2018-12-31', 0),
('2018-12-31', '2019-01-30', 0),

--less than one month with times
('2019-01-01 01:23:46', '2019-02-01 01:23:45', 0),
('2019-01-28 01:23:46', '2019-02-28 01:23:45', 0),
('2018-12-01 00:00:02', '2019-01-01 00:00:01', 0),
('2019-01-01 00:00:00', '2019-01-31 23:59:59', 0),
('2019-01-01 01:23:45', '2019-02-01 01:23:44', 0),
('2019-01-28 01:23:45', '2019-02-28 01:23:44', 0),
('2018-12-01 00:00:01', '2019-01-01 00:00:00', 0),
('2018-12-31 23:59:59', '2019-01-31 23:59:58', 0),

-- less than one month, as dateadd(month, 1, "January 31...") would make
('2019-01-31', '2019-02-28', 0),
('2019-01-31', dateadd(month, 1, '2019-01-31'), 0),

-- less than one month, as dateadd(month, 1, "January 31...") would make with times
('2019-01-31 23:59:59', '2019-02-28 23:59:59', 0),
('2019-01-31 23:59:59', dateadd(month, 1, '2019-01-31 23:59:59'), 0),

-- next day in the same month
('2019-01-01', '2019-01-02', 0),

-- next day in a different month
('2019-01-31', '2019-02-01', 0),

-- next day in a different year
('2018-12-31', '2019-01-01', 0),

-- next second in the same month
('2019-01-01 23:59:59', '2019-01-02 00:00:00', 0),

-- next second in a different month
('2019-01-31 23:59:59', '2019-02-01 00:00:00', 0),

-- next second in a different year
('2018-12-31 23:59:59', '2019-01-01 00:00:00', 0),

-- long ago in the same month
('1970-01-01', '2019-01-01', 588),

-- long ago in a consecutive month
('1970-01-01', '2019-02-01', 589),

-- long ago in a consecutive second
('1970-01-01 00:00:00', '2019-01-01 00:00:01', 588),
('1970-01-01 00:00:00', '2019-02-01 00:00:01', 589),
('1970-01-01 00:00:01', '2019-01-01 00:00:00', 587),
('1970-01-01 00:00:01', '2019-02-01 00:00:00', 588),

------------------------------------------------------------
-- everything above but in reverse order
-- :%s/^(\('.\+'\), \(.\+\), \(\d\+\)),$/(\2, \1, -\3),/
------------------------------------------------------------

-- exactly one month
('2019-02-01', '2019-01-01', -1),
('2019-02-28', '2019-01-28', -1),
('2019-01-01', '2018-12-01', -1),
('2019-01-31', '2018-12-31', -1),

-- exactly one month with times
('2019-02-01 01:23:45', '2019-01-01 01:23:45', -1),
('2019-02-28 01:23:45', '2019-01-28 01:23:45', -1),
('2019-01-01 00:00:01', '2018-12-01 00:00:01', -1),
('2019-01-31 23:59:59', '2018-12-31 23:59:59', -1),

-- less than one month
('2019-02-01', '2019-01-02', -0),
('2019-02-28', '2019-01-29', -0),
('2019-01-01', '2018-12-02', -0),
('2019-01-31', '2019-01-01', -0),
('2019-01-31', '2019-01-01', -0),
('2019-02-27', '2019-01-28', -0),
('2018-12-31', '2018-12-01', -0),
('2019-01-30', '2018-12-31', -0),

--less than one month with times
('2019-02-01 01:23:45', '2019-01-01 01:23:46', -0),
('2019-02-28 01:23:45', '2019-01-28 01:23:46', -0),
('2019-01-01 00:00:01', '2018-12-01 00:00:02', -0),
('2019-01-31 23:59:59', '2019-01-01 00:00:00', -0),
('2019-02-01 01:23:44', '2019-01-01 01:23:45', -0),
('2019-02-28 01:23:44', '2019-01-28 01:23:45', -0),
('2019-01-01 00:00:00', '2018-12-01 00:00:01', -0),
('2019-01-31 23:59:58', '2018-12-31 23:59:59', -0),

-- less than one month, as dateadd(month, 1, "January 31...") would make
('2019-02-28', '2019-01-31', -0),
(dateadd(month, 1, '2019-01-31'), '2019-01-31', -0),

-- less than one month, as dateadd(month, 1, "January 31...") would make with times
('2019-02-28 23:59:59', '2019-01-31 23:59:59', -0),
(dateadd(month, 1, '2019-01-31 23:59:59'), '2019-01-31 23:59:59', -0),

-- next day in the same month
('2019-01-02', '2019-01-01', -0),

-- next day in a different month
('2019-02-01', '2019-01-31', -0),

-- next day in a different year
('2019-01-01', '2018-12-31', -0),

-- next second in the same month
('2019-01-02 00:00:00', '2019-01-01 23:59:59', -0),

-- next second in a different month
('2019-02-01 00:00:00', '2019-01-31 23:59:59', -0),

-- next second in a different year
('2019-01-01 00:00:00', '2018-12-31 23:59:59', -0),

-- long ago in the same month
('2019-01-01', '1970-01-01', -588),

-- long ago in a consecutive month
('2019-02-01', '1970-01-01', -589),

-- long ago in a consecutive second
('2019-01-01 00:00:01', '1970-01-01 00:00:00', -588),
('2019-02-01 00:00:01', '1970-01-01 00:00:00', -589),
('2019-01-01 00:00:00', '1970-01-01 00:00:01', -587),
('2019-02-01 00:00:00', '1970-01-01 00:00:01', -588),

------------------------------------------------------------
-- and also the identical time
------------------------------------------------------------

('2019-01-01', '2019-01-01', 0);

with cte as (
select
	tests.*,
	case
		when 1=1
			and datepart(day, case when "end" > "start" then "end" else "start" end) >= datepart(day, case when "end" > "start" then "start" else "end" end)
			and datepart(hour, case when "end" > "start" then "end" else "start" end) >= datepart(hour, case when "end" > "start" then "start" else "end" end)
			and datepart(minute, case when "end" > "start" then "end" else "start" end) >= datepart(minute, case when "end" > "start" then "start" else "end" end)
			and datepart(second, case when "end" > "start" then "end" else "start" end) >= datepart(second, case when "end" > "start" then "start" else "end" end)
			and datepart(nanosecond, case when "end" > "start" then "end" else "start" end) >= datepart(nanosecond, case when "end" > "start" then "start" else "end" end)
			then datediff(month, "start", "end")
		else datediff(month, "start", "end") - sign(datediff(month, "start", "end"))
		end as "actual",
		datediff(month, "start", "end") "datediff",
		sign(datediff(month, "start", "end")) "sign"
from @tests tests
)
select
	*,
	case when "expected" = "actual" then N'✔ PASS' else N'❌ FAIL' end "result"
from cte
--where "expected" is null or "actual" is null or "expected" <> "actual"