sql server - Split date range to multiple rows using SQL -

i have table:

startdate enddate other_columns 1956-05-06 00:00:00.000 1960-04-05 00:00:00.000 myvalues

i need query return results as:

startdate enddate other_columns 1956-05-06 00:00:00.000 1956-12-31 00:00:00.000 myvalues 1957-01-01 00:00:00.000 1957-12-31 00:00:00.000 myvalues 1958-01-01 00:00:00.000 1958-12-31 00:00:00.000 myvalues 1959-01-01 00:00:00.000 1959-12-31 00:00:00.000 myvalues 1960-01-01 00:00:00.000 1960-04-05 00:00:00.000 myvalues

basically query explode rows yearly results. need start , end dates retained.

thanks!

create table #inputtable ( startdate datetime, enddate datetime, other_columns varchar(20)  )  insert #inputtable values('1956-05-06','1960-04-05','myvalues');  select * #inputtable 

output:

    startdate                 enddate                   other_columns     1956-05-06 00:00:00.000   1960-04-05 00:00:00.000   myvalues 

query:

create table #outputtable ( startdate datetime, enddate datetime, other_columns varchar(20)  )  declare @cnt int declare @startdate datetime declare @enddate datetime declare @incr int declare @tempdate datetime   set @startdate=(select startdate #inputtable) set @enddate=(select enddate #inputtable) set @cnt=datediff(yy,@startdate,@enddate) set @incr=0  set @tempdate=dateadd(yy,@incr,cast(@startdate datetime))  while @cnt>=0 begin     if @cnt = 0        begin          insert #outputtable values(@tempdate,@enddate,'myvalues');       end    else       begin          insert #outputtable values(@tempdate,dateadd(yy, datediff(yy,0,@tempdate)+1, -1),'myvalues');       end    set @tempdate=dateadd(yy,@incr+1,dateadd(yy,datediff(yy,0,@startdate),0))     set @cnt=@cnt-1    set @incr=@incr+1  end 

result : select * #outputtable;

startdate                 enddate                   other_columns 1956-05-06 00:00:00.000   1956-12-31 00:00:00.000   myvalues 1957-01-01 00:00:00.000   1957-12-31 00:00:00.000   myvalues 1958-01-01 00:00:00.000   1958-12-31 00:00:00.000   myvalues 1959-01-01 00:00:00.000   1959-12-31 00:00:00.000   myvalues 1960-01-01 00:00:00.000   1960-04-05 00:00:00.000   myvalues