Calculate difference in number of days between two dates in SQL Server

on Jan 15, 2013 03:41 AM

I want to calculate the number of days an employee has taken leave in the month of january.For that I have two date fields Leave_Start_Date and Leave_End_Date in the database as follows

Leave_Approval_Id         Leave_Start_Date                    Leave_End_Date             EmpId 
    1                   2013-01-25 00:00:00.000      2013-02-10 00:00:00.000                10

Here my problem is the Leave_End_Date is on february.So when i take the datedifference im getting the value as 16,but acctually i want is 6(in the month of january alone).

How can i do it ???

Download FREE API for Word, Excel and PDF in ASP.Net: Download
on Jan 16, 2013 03:52 AM


declare @tbl Table(Leave_Approval_Id  int, Leave_Start_Date   DateTime, Leave_End_Date  DateTime,  EmpId int)
insert into @tbl
select 1, '2013-01-25 00:00:00.000','2013-02-10 00:00:00.000', 10
select DateDiff(Day, Leave_Start_Date,  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) from @tbl