# DATEDIFF returns wrong value

• ### Question

• Hi,

I have a simple statement below, which I expect to return values quarterly.  But with the given dates below it returns 1 and I expect 0 because there is no quarter passed between them.  Can anyone suggest what's wrong with this:

```DECLARE @startDate AS DATE = '20090518'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(quarter, @startDate, @endDate)```

I am using British format, but that shouldn't affect as the above date declaration should handle that correctly.

Thanks

Wednesday, June 18, 2014 11:28 AM

• It looks at the absolute quarter values and gives the result

so your startdate quarter is 2 and enddate quarter is 3 which is why it gives quarter difference as 3-2 = 1

i think what you're looking at is this?

```DECLARE @startDate AS DATE = '20090518'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(month, @startDate, @endDate)/3```

• Proposed as answer by Wednesday, June 18, 2014 2:51 PM
• Marked as answer by Wednesday, June 18, 2014 4:30 PM
Wednesday, June 18, 2014 11:30 AM
• Datediff counts the number of boundaries passed. Thus:

DECLARE @startDate AS DATE = '20090630'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(quarter, @startDate, @endDate)

will return 1, as will

DECLARE @startDate AS DATE = '20090401'
DECLARE @endDate AS DATE = '20090930'

SELECT DATEDIFF(quarter, @startDate, @endDate)

If you want something else, you will need to compute for a lower granularity, for instance:

DECLARE @startDate AS DATE = '20090518'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(days, @startDate, @endDate) / 90

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Marked as answer by Wednesday, June 18, 2014 4:31 PM
Wednesday, June 18, 2014 1:20 PM

### All replies

• It looks at the absolute quarter values and gives the result

so your startdate quarter is 2 and enddate quarter is 3 which is why it gives quarter difference as 3-2 = 1

i think what you're looking at is this?

```DECLARE @startDate AS DATE = '20090518'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(month, @startDate, @endDate)/3```

• Proposed as answer by Wednesday, June 18, 2014 2:51 PM
• Marked as answer by Wednesday, June 18, 2014 4:30 PM
Wednesday, June 18, 2014 11:30 AM
• Datediff counts the number of boundaries passed. Thus:

DECLARE @startDate AS DATE = '20090630'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(quarter, @startDate, @endDate)

will return 1, as will

DECLARE @startDate AS DATE = '20090401'
DECLARE @endDate AS DATE = '20090930'

SELECT DATEDIFF(quarter, @startDate, @endDate)

If you want something else, you will need to compute for a lower granularity, for instance:

DECLARE @startDate AS DATE = '20090518'
DECLARE @endDate AS DATE = '20090701'

SELECT DATEDIFF(days, @startDate, @endDate) / 90

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
• Marked as answer by Wednesday, June 18, 2014 4:31 PM
Wednesday, June 18, 2014 1:20 PM