How to find the number of days between two different dates in SQL Server?

Question

I have basic query:

SELECT A.Username
    ,EH.[From]
    ,EH.[End]
    ,DATEDIFF(d, [From], [End]) AS HolidaysInDays
FROM EmployeHoliday EH
LEFT JOIN Admin A ON EH.UserId = A.AdminId
WHERE EH.IsActive = 1

If employee apply leave(LeaveStart = "17 Dec 2019" to LeaveEnd = "19 Dec 2019"), when I execute my query the output is HolidaysInDays = 2 but the actual Days is 3. So, how can I resolve this problem?

My output is as shown in the below image.

Your underlying data is exactly 2 days anyway when time parts are considered. Can people take a half day off? If so how is that represented in `EmployeHoliday`? — Martin Smith, Dec 20 '19 at 09:48

score 1 · Answer 1 · answered Dec 20 '19 at 09:43

When using DATEDIFF, the end date is an exclusive date so is not factored into the calculation:

PRINT DATEDIFF(d, '2019-12-17', '2019-12-19')

Output:

To add the final date in, just add 1 to the result:

PRINT DATEDIFF(d, '2019-12-17', '2019-12-17') + 1
PRINT DATEDIFF(d, '2019-12-17', '2019-12-18') + 1
PRINT DATEDIFF(d, '2019-12-17', '2019-12-19') + 1

Output:

1
2
3

score 0 · Answer 2 · answered Dec 20 '19 at 10:52

0

Think of it like this: what is 7 - 5?

Boom.

answered Dec 20 '19 at 10:52

Excelnoobster

129
7

How to find the number of days between two different dates in SQL Server?

2 Answers2