Transform one record into several records with date

1

Hello ,

I have records with an starting date and an ending date. Both can be on the same year or in different years.

I have to convert that record in so many records as years between the ending date and the starting date.

For example:

START DATE END DATE
05/03/2013 05/01/2015

I want to transform it into:

START DATE END DATE
05/03/2013 31/12/2013
01/01/2014 31/12/2014
01/01/2015 05/01/2015

Does anyone knows how to transform this record into these?

Thanks in advance ,

2

You should have a calendar table .This will help you.

;with 
  calendar as (select CAST('20120101' as date) as first_date, cast('20121231' as date) as last_date UNION ALL
               select CAST('20130101' as date), cast('20131231' as date) UNION ALL
               select CAST('20140101' as date), cast('20141231' as date) UNION ALL
               select CAST('20150101' as date), cast('20151231' as date) UNION ALL
               select CAST('20160101' as date), cast('20161231' as date) )
 , yourSource as (select  cast('20130305' as date) as start_date, cast('20150105' as date) as end_date)


select 
   case when s.start_date>= c.first_date then s.start_date else c.first_date end as start_date
   ,case when s.end_date <= c.last_date then s.end_date else c.last_date end as end_date
from calendar as c
     cross join yourSource as s
where
  s.start_date <= c.last_date 
  and 
  s.end_date>=c.first_date

I have the calendar table, that holds the list of years with the first day and last day in that year.

output of it:

start_date end_date
05/03/2013 00:00:00 31/12/2013 00:00:00
01/01/2014 00:00:00 31/12/2014 00:00:00
01/01/2015 00:00:00 05/01/2015 00:00:00

dbfiddle here

3

Thank you very much , nice code!