CODEWITHSUNDEEP
sqlsql-serverdatedatetime
Mr. Bhosale
Mr. Bhosale

Reputation: 3096

SQL Date Difference in Days Result Wrong

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 ?

Upvotes: 3

Views: 1125

Answers (4)

p4n1
p4n1

Reputation: 117

This is because of the extra day that is not computed in the second case. To explain why it happens in your first query

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

you are doing something similar to: 1 - 380 = 379.

While in the second

it's (1-365) + (1-15) = 364 + 14 = 378.

Depending on what you are after

it might be a better idea to break it down as:

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

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

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')

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269633

The correct way to split into two sections is to repeat the date:

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

Your version skips a day, so it is not at all surprising that it is off by a day.

Upvotes: 3

George Menoutis
George Menoutis

Reputation: 7240

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.

Upvotes: 4

Related Questions