SQL Date Difference in Days Result Wrong

Question

I want to calculate day difference between two dates.

When i calcualte Differane between '2017-01-01' and '2018-01-15' its 379 Days.

SELECT DATEDIFF(DD,'2017-01-01','2018-01-15') 

But when i split dates into two sections with addition .then i Got result 378.

select DATEDIFF( DD,'2017-01-01', '2017-12-31') + DATEDIFF( DD,'2018-01-01', '2018-01-15') 

anyone explain me why this happen ?

Answer 1

This is very simple Math:

I:  <a,b>
II: <a,c>,<c+1,b>   where c is between a and b

I      II
b-a != b-(c+1) + c-a
b-a != b-c-1 + c-a
b-a != b-a-1       -- here is 1 missing day

To make it right you could use:

select DATEDIFF( DD,'2017-01-01', '2018-01-01') 
     + DATEDIFF( DD,'2018-01-01', '2018-01-15') 

Answer 2

Datediff counts the boundaries of the datetype between the two dates.

The boundary "end of '2017-12-31'-start of '2018-01-01'" is included in the first case, but not the second.