Number of days between two dates that don't have years

 Getting number of days between two dates is simple in SQL, write as :

SELECT DATEDIFF(DD,FromDate,ToDate)

And you will get the days count between these dates.

But on msdn forum I found an user had issue, which I am adding here is :

A cattle rancher buys cattle, holds them for 10 - 12 months and then sells them.  During the summer, he grazes them which significantly lowers his production cost (grass is much cheaper than feeding corn).  The dates being used for summer grazing are 5/15 to 9/20, regardless of the year.  The buy and sell dates can vary throughout a calendar year (see sample data).

Now, some sample data:

DECLARE
@LowExpensePeriod TABLE (StartDate NVARCHAR(6), EndDate NVARCHAR(6));

INSERT INTO @LowExpensePeriod (StartDate, EndDate) VALUES('5/15', '9/20');

SELECT * FROM
@LowExpensePeriod;



DECLARE @Trx TABLE (Buy date, Sell date);

INSERT INTO @Trx (Buy, Sell) VALUES



--dates fall in
same year



('6/1/2012', '9/1/2012'),   --All days between
low expense period

('3/1/2012', '10/30/2012'), --Each side of low expense period

('3/1/2012', '8/31/2012'),  --Left side of low
expense period

('5/25/2012', '10/30/2012'),--Right side of low expense period

--dates span two
years

('8/10/2012', '6/1/2013');  --From each year in
low expense period



SELECT *, DATEDIFF(d, Buy, Sell) AS HoldingPeriod, '?' AS
DaysInLowExpensePeriod FROM @Trx;

The question - how is it possible to compute the number of days in the low expense period accurately for each of the sample Buy+Sell date scenarios? [The goal is to replace the '?' in the column called DaysInLowExpensePeriod with the number of days that fall in the low expense period for each set of Buy+Sell dates].

Below is the Suggested answer :

SELECT
*

 ,DATEDIFF(DD, BuyDate, SellDate) AS HoldingPeriod

 ,CASE WHEN DATEDIFF(DD,BuyDate,'9/20/'+STR(YEAR(BuyDate)))>0 THEN

    CASE WHEN DATEDIFF(DD,BuyDate,'5/15/'+STR(YEAR(BuyDate)))>0 THEN 128

    ELSE DATEDIFF(DD,BuyDate,'9/20/'+ STR(YEAR(BuyDate))) END

  ELSE
0 END

 +CASE WHEN DATEDIFF(DD,'5/15/'+STR(YEAR(SellDate)),SellDate)>0 THEN

   CASE WHEN DATEDIFF(DD,'9/20/'+STR(YEAR(SellDate)),SellDate)>0 THEN 128

   ELSE DATEDIFF(DD,'5/15/'+STR(YEAR(SellDate)),SellDate) END

  ELSE 0 END

 +CASE WHEN DATEDIFF(YY,BuyDate,SellDate)>0 THEN (DATEDIFF(YY,BuyDate,SellDate)-1)*128 

  ELSE 0 END AS DaysInLowExpensePeriod 

FROM
@Trx;

Reference: Govind Badkur(http://govindbadkur.blogspot.com)