How to get the Year in between two dates

Question

Dear I have leave table The Fields are EmpNo EmpName LeaveStart LeaveEnd I want to show the leave taken by year. i use datediff(ddLeaveStartLeaveEnd) it will shows the no. of days have taken. but i want to show yearly wise. E.g 2014 - 10 days 2015 - 16 days how & where i can use year function becoz have leavestart & leaveend suppose if employee taken a leave LeavStart- 28-Dec-2015 LeaveEnd - 05-Jan-2016 then result should 2015 - 4 days 2016 - 5 days then how to use query. please help on this Thanks Basit.

Dharmesh Singh · Answer

I hope this code help you. If it is works then marked as answer DECLARE @start DATETIME @end DATETIME; SELECT @start = '20121126' @end = '20130110'; CREATE tABLE #TEMPDate (id int identity(11)DateWise datetime) insert into #TEMPDate(DateWise) SELECT TOP (DATEDIFF(DAY @start @end)+1) DATEADD(DAY ROW_NUMBER() OVER (ORDER BY name)-1 @start) FROM sys.all_columns select COUNT(*)Year(DateWise) from #TEMPDate group by Year(DateWise) drop TABLE #TEMPDate

Pankaj Kumar Choudhary · Answer

Hello! Basit Khan Try This DECLARE @start DATETIME @end DATETIME; SELECT @start = '28-Dec-2015' @end = '05-Jan-2016'; select year(dt) [Year] count(*) [Numer_Of_Days] from (select top(datediff(d DATEADD(Day-1 @start) @end)) dateadd(d row_number() over (order by (select null)) DATEADD(Day-1 @start)) dt from sys.columns) q group by year(dt) order by [Year] Output: