CODEWITHSUNDEEP
sqlsql-server
test canada
test canada

Reputation: 3

get records of interval of dates between start date and end date

I have two dates start date and end date . How can I know if between these two dates , I got 01/01 meaning new year .

For example :

Table test 
id  start date    End date 

1   02/03/2013   19/09/2016
2   15/02/2015   30/06/2015

Output 

id  start date    End date  
1   02/03/2013   31/12/2013
1   01/01/2014   31/12/2014
1   01/01/2015   31/12/2015
1   01/01/2016   19/09/2016
2   15/02/2015   30/06/2015

How to do something like that ?

Upvotes: 0

Views: 107

Answers (2)

John Bell
John Bell

Reputation: 2350

You can use a recursive CTE to find all years between the two dates, then cast out the dates to DATETIME objects to get the desired output:

;WITH CTE AS (
SELECT ID, DATEPART(YEAR, STARTDATE) AS YR, ENDDATE
FROM TABLE1
UNION ALL
SELECT ID, YR+1, ENDDATE
FROM CTE
WHERE YR < DATEPART(YEAR,ENDDATE))
SELECT A.ID, CASE WHEN CAST(A.YR AS VARCHAR)+'-01-01' > B.STARTDATE THEN CAST(A.YR AS VARCHAR)+'-01-01' ELSE B.STARTDATE END AS STARTDATE, CASE WHEN CAST(A.YR AS VARCHAR)+'-12-31' < B.ENDDATE THEN CAST(A.YR AS VARCHAR)+'-12-31' ELSE B.ENDDATE END AS ENDDATE
FROM CTE AS A
JOIN TABLE1 AS B
ON A.ID=B.ID
ORDER BY A.ID

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You would know if the year component is different:

select t.*,
       (case when year(startdate) < year(enddate) then 1
             else 0
        end) as HasNewYear
from table t;

Upvotes: 1

Related Questions