January 10, 2020 at 4:42 pm
I'm trying to figure out what the best way to calculate the number of days between two dates, but I want to exclude the extra day in leap years. What I've googled says to use a date table for this. This seems like overkill to me when most of the time a simple Datdiff is going to get the right number of days.
So I thought I would throw this idea out and see what kind of response I get.
Should I just use Datediff, then somehow determine if the leap day is within the range, then subtract 1? I doubt I would ever have the situation where the date range would be over more than one leap year day.
DECLARE @Eff_DateDATE;
DECLARE @Exp_DateDATE;
SET @Eff_Date = '01/01/2020';
SET @Exp_Date = '01/01/2021';
SELECT DATEDIFF(d, @Eff_Date, @Exp_Date) AS Date_diff;
-- Results 366
-- I would want 365
SET @Eff_Date = '01/01/2020';
SET @Exp_Date = '07/01/2020';
SELECT DATEDIFF(d, @Eff_Date, @Exp_Date) AS Date_diff;
-- Results 182
-- I would want 181
This calculation would need to happen in a batch process at night. Calculating the number of days in an insurance policy term.
Thanks in advance for any thoughts you have on this.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 10, 2020 at 5:24 pm
Maybe I just needed to post something to get the brain going.
Here is what I came up with. I borrowed some code I found at https://www.mssqltips.com/sqlservertip/1527/sql-server-function-to-determine-a-leap-year/
DECLARE @Eff_DateDATE;
DECLARE @Exp_DateDATE;
SET @Eff_Date = '01/01/2020';
SET @Exp_Date = '01/01/2021';
SELECT DATEDIFF(d, @Eff_Date, @Exp_Date) AS Date_diff;
-- Results 366
-- I would want 365
SET @Eff_Date = '01/01/2020';
SET @Exp_Date = '07/01/2020';
SELECT DATEDIFF(d, @Eff_Date, @Exp_Date) AS Date_diff;
-- Results 182
-- I would want 181
/*
-- determine if a year is a leap year
SELECT
CASE DATEPART(mm, DATEADD(dd, 1, CAST((CAST(@year AS VARCHAR(4)) + '0228') AS DATE)))
WHEN 2 THEN 1
ELSE
0
END
;
*/
SELECT DATEDIFF(d, @Eff_Date, @Exp_Date) -
(CASEWHEN CAST((CAST(YEAR(@Eff_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THENCASE DATEPART(mm, DATEADD(dd, 1, CAST((CAST(YEAR(@Eff_Date) AS VARCHAR(4)) + '0228') AS DATE)))
WHEN 2 THEN 1
ELSE
0
END
WHEN CAST((CAST(YEAR(@Exp_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THENCASE DATEPART(mm, DATEADD(dd, 1, CAST((CAST(YEAR(@Exp_Date) AS VARCHAR(4)) + '0228') AS DATE)))
WHEN 2 THEN 1
ELSE
0
END
ELSE
0
END) AS Date_diff
;
This seems to do the trick in initial testing, I'll do a little more to be sure.
Would you create this or part of it(looking at the last bit of SQL posted) as a function?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 10, 2020 at 5:48 pm
Of course, if I would read through the comments(at the link I posted), Jeff Moden would have a simpler way to determine a leap year.
SELECT
CAST(DATEDIFF(d, @Eff_Date, @Exp_Date) AS INT) -
CAST(CASEWHEN CAST((CAST(YEAR(@Eff_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN ISDATE(CAST(YEAR(@Eff_Date) AS VARCHAR(4))+'0229')
WHEN CAST((CAST(YEAR(@Exp_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN ISDATE(CAST(YEAR(@Eff_Date) AS VARCHAR(4))+'0229')
ELSE
0
END AS INT) AS Date_diff
;
Updated SQL, needed the CAST as Varchar on the Year.
Thanks Jeff.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 10, 2020 at 6:03 pm
If you install this table valued function you could do it like this:
DECLARE @Eff_DateDATE;
DECLARE @Exp_DateDATE;
SET @Eff_Date = '01/01/2020';
SET @Exp_Date = '01/01/2021';
SELECT COUNT(Value) AS Days
FROM dbo.DateRange(@Eff_Date,@Exp_Date,'dd',1)
WHERE DATEPART(mm, Value) <> 2
OR DATEPART(dd, Value) <> 29
PS: You might need to subtract 1 from the result as it includes both the start and end intervals.
January 10, 2020 at 7:51 pm
Thanks for the feedback and info Jonathan, I'll take a look at it.
Found lots of holes in the prior SQL I posted, here is my revised SQL:
DECLARE @Eff_DateDATE;
DECLARE @Exp_DateDATE;
SET @Eff_Date = '12/01/2019';
SET @Exp_Date = '05/01/2021';
SELECT DATEDIFF(d, @Eff_Date, @Exp_Date) AS Date_diff;
-- Results 517
-- I would want 516
SELECT
CAST(DATEDIFF(d, @Eff_Date, @Exp_Date) AS INT) -
CAST(CASEWHEN CAST((CAST(YEAR(@Eff_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THENCASEISDATE(CAST(YEAR(@Eff_Date) AS VARCHAR(4))+'0229')
WHEN 1 THEN1
ELSE
CASEWHEN CAST((CAST(YEAR(@Exp_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN ISDATE(CAST(YEAR(@Exp_Date) AS VARCHAR(4))+'0229')
ELSE
0
END
END
WHEN CAST((CAST(YEAR(@Exp_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THENCASEWHEN ISDATE(CAST(YEAR(@Exp_Date) AS VARCHAR(4))+'0229') = 1
THEN 1
WHEN CAST((CAST((YEAR(@Eff_Date) +1) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN ISDATE(CAST((YEAR(@Eff_Date) + 1) AS VARCHAR(4))+'0229')
ELSE
0
END
WHEN CAST((CAST((YEAR(@Eff_Date) +1) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN ISDATE(CAST((YEAR(@Eff_Date) + 1) AS VARCHAR(4))+'0229')
ELSE
0
END AS INT) AS Date_diff
;
-- Results 516
I know we can have 18 month policies, I don't think they can be longer than that.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 10, 2020 at 9:00 pm
Here is an option...
Declare @startDate datetime = '2020-01-01'
, @endDate datetime = '2021-01-01';
With dates (y, StartDate, EndDate, Feb28)
As (
Select Top (year(@endDate) - year(@startDate) + 1)
y
, iif(@startDate > YearStart, @startDate, YearStart)
, iif(@endDate < YearEnd, @endDate, YearEnd)
, Feb28
From (Values (0), (1), (2), (3), (4), (5), (6)) As n(n)
Cross Apply (Values (year(@startDate) + n)) As y(y)
Cross Apply (Values (
datetimefromparts(y, 2, 28, 0, 0, 0, 0)
, datetimefromparts(y, 1, 1, 0, 0, 0, 0)
, datetimefromparts(y, 12, 31, 0, 0, 0, 0))) As yy(Feb28, YearStart, YearEnd)
)
Select *, Date_Diff = datediff(day, d.StartDate, d.EndDate)
, Adjusted_Date_Diff = datediff(day, d.StartDate, d.EndDate)
- iif(d.StartDate <= d.Feb28 And d.EndDate > d.Feb28, l.ly, 0)
+ iif(d.StartDate = d.EndDate, 1, 0)
From dates As d
Cross Apply (Values (iif((y % 400 = 0 Or y % 4 = 0)
And y % 100 <> 0, 1, 0))) As l(ly);
To get the totals - change it to:
Select Date_Diff = sum(datediff(day, d.StartDate, d.EndDate))
, Adjusted_Date_Diff = sum(datediff(day, d.StartDate, d.EndDate)
- iif(d.StartDate <= d.Feb28 And d.EndDate > d.Feb28, l.ly, 0)
+ iif(d.StartDate = d.EndDate, 1, 0))
From dates As d
Cross Apply (Values (iif((y % 400 = 0 Or y % 4 = 0)
And y % 100 <> 0, 1, 0))) As l(ly);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 11, 2020 at 1:23 am
This will work for all dates between 1800 and 2400, it's more efficient than my previous proposed method
DECLARE @Eff_DateDATE;
DECLARE @Exp_DateDATE;
SET @Eff_Date = '01/01/2020';
SET @Exp_Date = '01/01/2021';
SELECT DATEDIFF(dd,@Eff_Date,@Exp_Date)
- (SELECT COUNT(*)
FROM (VALUES ('18040229'),('18080229'),('18120229'),('18160229'),('18200229'),('18240229'),('18280229'),
('18320229'),('18360229'),('18400229'),('18440229'),('18480229'),('18520229'),('18560229'),('18600229'),('18640229'),
('18680229'),('18720229'),('18760229'),('18800229'),('18840229'),('18880229'),('18920229'),('18960229'),('19040229'),
('19080229'),('19120229'),('19160229'),('19200229'),('19240229'),('19280229'),('19320229'),('19360229'),('19400229'),
('19440229'),('19480229'),('19520229'),('19560229'),('19600229'),('19640229'),('19680229'),('19720229'),('19760229'),
('19800229'),('19840229'),('19880229'),('19920229'),('19960229'),('20000229'),('20040229'),('20080229'),('20120229'),
('20160229'),('20200229'),('20240229'),('20280229'),('20320229'),('20360229'),('20400229'),('20440229'),('20480229'),
('20520229'),('20560229'),('20600229'),('20640229'),('20680229'),('20720229'),('20760229'),('20800229'),('20840229'),
('20880229'),('20920229'),('20960229'),('21040229'),('21080229'),('21120229'),('21160229'),('21200229'),('21240229'),
('21280229'),('21320229'),('21360229'),('21400229'),('21440229'),('21480229'),('21520229'),('21560229'),('21600229'),
('21640229'),('21680229'),('21720229'),('21760229'),('21800229'),('21840229'),('21880229'),('21920229'),('21960229'),
('22040229'),('22080229'),('22120229'),('22160229'),('22200229'),('22240229'),('22280229'),('22320229'),('22360229'),
('22400229'),('22440229'),('22480229'),('22520229'),('22560229'),('22600229'),('22640229'),('22680229'),('22720229'),
('22760229'),('22800229'),('22840229'),('22880229'),('22920229'),('22960229'),('23040229'),('23080229'),('23120229'),
('23160229'),('23200229'),('23240229'),('23280229'),('23320229'),('23360229'),('23400229'),('23440229'),('23480229'),
('23520229'),('23560229'),('23600229'),('23640229'),('23680229'),('23720229'),('23760229'),('23800229'),('23840229'),
('23880229'),('23920229'),('23960229'),('24000229')) T(Feb29)
WHERE T.Feb29 BETWEEN @Eff_Date AND @Exp_Date)
January 11, 2020 at 7:26 am
This can be made more dynamic with a function that gets the FEB29 days
CREATE FUNCTION dbo.GetLeapYearDates(@Date1 AS date, @Date2 AS date)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0)) AS X(N))
, Nums AS (SELECT TOP(ABS(YEAR(@Date2) - YEAR(@Date1)) +1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum
FROM T T1 -- Max = 64
, T T2 -- Max = 4,096
, T T3 -- Max = 262,144
)
SELECT DATEADD(DD, 59, DATEADD(YY, (SELECT MIN(YEAR(dt)) FROM (VALUES (@Date1), (@Date2)) AS v(dt)) + rownum -1901, 0)) AS LongFeb
FROM Nums
WHERE DAY(DATEADD(DD, 59, DATEADD(YY, (SELECT MIN(YEAR(dt)) FROM (VALUES (@Date1), (@Date2)) AS v(dt)) + rownum -1901, 0))) = 29;
GO
DECLARE @Eff_DateDATE;
DECLARE @Exp_DateDATE;
SET @Eff_Date = '2020-01-01';
SET @Exp_Date = '2021-01-01';
SELECT DATEDIFF(dd,@Eff_Date,@Exp_Date)
- (SELECT COUNT(*)
FROM dbo.GetLeapYearDates(@Eff_Date, @Exp_Date))
January 11, 2020 at 4:30 pm
If you want a solution that can have any start/end date for any range - between 1753 and 9999:
Declare @startDate datetime = '1753-01-01'
, @endDate datetime = '9999-12-31';
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, inputYears (y)
As (
Select Top (year(@endDate) - year(@startDate) + 1)
year(@startDate) + row_number() over(Order By @@spid) - 1
From t t1, t t2, t t3, t t4
)
, dates (LeapYear, StartDate, EndDate, Feb28)
As (
Select iif(y % 400 = 0 Or y % 4 = 0 And y % 100 <> 0, 1, 0)
, iif(@startDate > YearStart, @startDate, YearStart)
, iif(@endDate < YearEnd, @endDate, YearEnd)
, Feb28
From inputYears As y
Cross Apply (Values (datetimefromparts(y, 2, 28, 0, 0, 0, 0)
, datetimefromparts(y, 1, 1, 0, 0, 0, 0)
, datetimefromparts(y, 12, 31, 0, 0, 0, 0))) As dt(Feb28, YearStart, YearEnd)
)
Select *
, Date_Diff = datediff(day, d.StartDate, d.EndDate)
, Adjusted_Date_Diff = datediff(day, d.StartDate, d.EndDate)
- iif(d.StartDate <= d.Feb28 And d.EndDate > d.Feb28, d.LeapYear, 0)
+ iif(d.StartDate = d.EndDate, 1, 0)
From dates As d;
And if you want - for some reason - to be able to get any date range between 0001 and 9999:
Declare @startDate date = '0001-01-01'
, @endDate date = '9999-12-31';
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
, (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, inputYears (y)
As (
Select Top (year(@endDate) - year(@startDate) + 1)
year(@startDate) + row_number() over(Order By @@spid) - 1
From t t1, t t2, t t3, t t4
)
, dates (LeapYear, StartDate, EndDate, Feb28)
As (
Select iif(y % 400 = 0 Or y % 4 = 0 And y % 100 <> 0, 1, 0)
, iif(@startDate > YearStart, @startDate, YearStart)
, iif(@endDate < YearEnd, @endDate, YearEnd)
, Feb28
From inputYears As y
Cross Apply (Values (datefromparts(y, 2, 28, 0, 0, 0, 0)
, datefromparts(y, 1, 1, 0, 0, 0, 0)
, datefromparts(y, 12, 31, 0, 0, 0, 0))) As dt(Feb28, YearStart, YearEnd)
)
Select *
, Date_Diff = datediff(day, d.StartDate, d.EndDate)
, Adjusted_Date_Diff = datediff(day, d.StartDate, d.EndDate)
- iif(d.StartDate <= d.Feb28 And d.EndDate > d.Feb28, d.LeapYear, 0)
+ iif(d.StartDate = d.EndDate, 1, 0)
From dates As d;
You will also notice that DATEDIFF only counts 364 days for a normal year - and whether or not that is valid depends on how you define your term dates. It appears you define the term date as non-inclusive so a term date of 01/01/2021 shows that policy to no longer be in effect as of the start of that date.
If the term date is defined as inclusive - meaning the policy/contract is effective until the end of the specified date you would need to add one to the calculation for each year so that a normal year would count as 365 and a leap year would count as 366.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 13, 2020 at 3:54 pm
I like the simplicity of finding the February 29 dates, then subtracting that DesNorton posted. But it didn't calculate correctly when I used the start date of 01/01/2019 end date of 01/01/2020. The code still returned the 02/29/2020 date.
I modified the code to look like this:
ALTER FUNCTION dbo.GetLeapYearDates(@Date1 AS date, @Date2 AS date)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
--DECLARE @Date1DATE;
--DECLARE @Date2DATE;
--SET @Date1 = '2015-01-01';
--SET @Date2 = '2021-01-01';
WITH
T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)
, (0),(0),(0),(0)) AS X(N))
, Nums AS (SELECT TOP(ABS(YEAR(@Date2) - YEAR(@Date1)) +1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum
FROM T T1 -- Max = 64
, T T2 -- Max = 4,096
, T T3 -- Max = 262,144
)
,
LeapDays AS
(
SELECT DATEADD(DD, 59, DATEADD(YY, (SELECT MIN(YEAR(dt)) FROM (VALUES (@Date1), (@Date2)) AS v(dt)) + rownum -1901, 0)) AS LongFeb
FROM Nums
WHERE DAY(DATEADD(DD, 59, DATEADD(YY, (SELECT MIN(YEAR(dt)) FROM (VALUES (@Date1), (@Date2)) AS v(dt)) + rownum -1901, 0))) = 29
)
SELECT LongFeb
FROM LeapDays
WHERE LongFeb BETWEEN @Date1 AND @Date2
;
GO
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
January 13, 2020 at 5:07 pm
I like the simplicity of finding the February 29 dates, then subtracting that DesNorton posted. But it didn't calculate correctly when I used the start date of 01/01/2019 end date of 01/01/2020. The code still returned the 02/29/2020 date.
Good catch
January 13, 2020 at 6:37 pm
I think the code below is much simpler. The code is complete, no function needed. And, yes, I took a short-cut, so, yes, this code will fail for 2100. If you want to address that, let me know and we can add the necessary checks for that too.
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number
FROM cte_tally10 c1
CROSS JOIN cte_tally10 c2
)
SELECT Eff_Date, Exp_Date,
DATEDIFF(DAY, Eff_Date, Exp_Date) - leap_days_count AS Date_diff
, leap_days_count /*just to show the result, naturally drop it from final code*/
FROM ( VALUES(1,CAST('20200101' AS date),CAST('20210101' AS date)),
(2,'20200101','20200701'),
(3,'20200101','20200228'), (4,'20200101','20200229'),
(9,'20011114','20190223') /* note: 17 year+ range */
) AS test_dates(date_id, Eff_Date, Exp_Date)
CROSS APPLY (
SELECT ISNULL(SUM(CASE WHEN year % 4 = 0 THEN 1 ELSE 0 END), 0) AS leap_days_count
FROM (
SELECT YEAR(Eff_Date) + t.number AS year
FROM cte_tally100 t
WHERE t.number <= YEAR(Exp_date) - YEAR(Eff_Date)
) AS all_years
WHERE DATEADD(DAY, 59, DATEFROMPARTS(year, 1, 1)) BETWEEN Eff_Date AND Exp_Date
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply