# Leap year difficulties... Crossing February 29, 20xx

I Work in the insurance world and have a problem dealing with Feb 29.. Not just a leap year but the actual date of feb29. My current formula says...

IF  (datediff('day',pol2exp,pol2eff)and<365) and (datediff('day',polteff,originalnewdate)and<365) THEN New ELSE Renewal END

SO the formula works 99.9% of the time. Here is what the formula is actually doing.  The first datediff looks at last years policy and says am I less than 365. A policy is typically exactly 365. For example, 1/1/2015 - 1/1/2016 or 3/1/2015 - 3/1/2016. A policy can start on any date and end on any date, but sometimes a policy is 6 months. The first 12 months of a policy is new and any transaction after 12 months is renewal.

Here is an example of the issue..  Feb 29, 2012 is a leap year date.

The first date is poleffdate and the second date is polexpdate

Policy A -  9/1/2011 - 3/1/2012 a six month policy
Policy B - 3/1/2012 - 9/1/2012 another six month policy.

Policy A passes the first test of being less than 365 days, but policy B will fail because I will bill the last day of that policy. I will issue the final bill on 9/1/2012 (polteff). Polteff is 366 days away from Policy A so it falls into the renewal category and s/b new.

Second example,

Policy A -  3/1/2012 - 9/1/2012 a six month policy
Policy B - 9/1/2012 - 3/1/2013 another six month policy.

Policy A passes the first test of being less than 365 days, and policy B will pass because it is less the 365 from one. I will issue the final bill on 9/1/2013 (polteff). Polteff is 365 days away from Policy A so it falls into the New category where it belongs.

It is the day of February 29th and not just the leap year. When both policies cross Feb 29, I'll get 366 on the second policy and it falls into renewal and not new.

SO I need something that works with the formula above where February 29th is between poleffdate and polexpdate of policy A. and  February 29th is between poleffdate and polexpdate of policy B. then the datediff('day',polteff,originalnewdate) can be 366

otherwise my original formula will work.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Did you know that 90+% of the world's population don't use m/d/yyyy as their preferred date format? It's not a huge deal, but yyyy-mm-dd is a way easier format to use for a potentially international audience (like me).

OK. putting that aside: In both your examples, I presume they are for the same person. e.g,

sample 1
FRED Policy A -  2011-09-01 - 2012-03-01 a six month policy
FRED Policy B - 2012-03-01 - 2012-09-01 another six month policy.

sample2
FRED Policy A -  2012-03-01 - 2012-09-01 a six month policy
FRED Policy B - 2012-09-01 - 2013-03-01 another six month policy.

and I'm not seeing how those rows work into the formula that references:
pol2exp, pol2eff, polteff ,originalnewdate

Could you rework those examples into source table rows, using the column names of the tables.
perhaps this will assist. Note a CROSS APPLY is used so that the alias [days_in_year] can be reused as if it is a column
``````SELECT
[pol2exp]
, [pol2eff]
, ca.days_in_year
, datediff(day,pol2exp,pol2eff)
, CASE WHEN datediff(day,pol2exp,pol2eff) < ca.days_in_year
THEN 'renew'
ELSE 'new'
END
FROM table1
CROSS APPLY (
SELECT
CASE WHEN ISDATE(CAST(YEAR(pol2eff) AS char(4)) + '0229') = 1
THEN 366
ELSE 365
END
) ca (days_in_year)
``````
Using some dummy data:
``````CREATE TABLE Table1
([blah] varchar(130), [pol2exp] datetime, [pol2eff] datetime, [blah2] varchar(250))
;

INSERT INTO Table1
([blah], [pol2exp], [pol2eff], [blah2])
VALUES
('FRED Policy A', '2011-09-01 00:00:00', '2012-03-01 00:00:00', 'a six month policy'),
('FRED Policy B', '2012-03-01 00:00:00', '2012-09-01 00:00:00', 'another six month policy.'),
('FRED Policy A', '2012-03-01 00:00:00', '2012-09-01 00:00:00', 'a six month policy'),
('FRED Policy B', '2012-09-01 00:00:00', '2013-03-01 00:00:00', 'another six month policy.')
;
``````
I produced this result:
``````|                     pol2exp |                     pol2eff | days_in_year |     |       |
|-----------------------------|-----------------------------|--------------|-----|-------|
| September, 01 2011 00:00:00 |     March, 01 2012 00:00:00 |          366 | 182 | renew |
|     March, 01 2012 00:00:00 | September, 01 2012 00:00:00 |          366 | 184 | renew |
|     March, 01 2012 00:00:00 | September, 01 2012 00:00:00 |          366 | 184 | renew |
| September, 01 2012 00:00:00 |     March, 01 2013 00:00:00 |          365 | 181 | renew |
``````
see: http://sqlfiddle.com/#!3/1c261/1

Experts Exchange Solution brought to you by