locked
DATEDIFF returns wrong value RRS feed

  • 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

Answers

  • 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


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Ah Mad Wednesday, June 18, 2014 2:51 PM
    • Marked as answer by Alex Teslin 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 Alex Teslin 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


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Ah Mad Wednesday, June 18, 2014 2:51 PM
    • Marked as answer by Alex Teslin 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 Alex Teslin Wednesday, June 18, 2014 4:31 PM
    Wednesday, June 18, 2014 1:20 PM
  • Thanks for your replies,

    I will go with Visakh16's answer as it uses month, which I think is more precise as sometimes there could be less or more than 90 days within the quarter period.

    Wednesday, June 18, 2014 4:30 PM