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_Date DATE;
DECLARE @Exp_Date DATE;
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_Date DATE;
DECLARE @Exp_Date DATE;
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) -
(CASE WHEN CAST((CAST(YEAR(@Eff_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN CASE 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
THEN CASE 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(CASE WHEN 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_Date DATE;
DECLARE @Exp_Date DATE;
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_Date DATE;
DECLARE @Exp_Date DATE;
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(CASE WHEN CAST((CAST(YEAR(@Eff_Date) AS VARCHAR(4)) + '0228') AS DATE) BETWEEN @Eff_Date AND @Exp_Date
THEN CASE ISDATE(CAST(YEAR(@Eff_Date) AS VARCHAR(4))+'0229')
WHEN 1 THEN 1
ELSE
CASE WHEN 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
THEN CASE WHEN 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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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_Date DATE;
DECLARE @Exp_Date DATE;
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_Date DATE;
DECLARE @Exp_Date DATE;
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
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 @Date1 DATE;
--DECLARE @Date2 DATE;
--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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply