Share via

DATEDIFF (YEAR, StartDate, EndDate) returns incorrectly

Bobby P 271 Reputation points
2024-03-28T13:03:43.4066667+00:00

I am trying to figure out why the DATEDIFF(YEAR) Function is returning incorrectly and am wondering if I have to get the StartDate and EndDate in a certain format before attempting to use the function.

SELECT DATEDIFF(YEAR, '2023-12-31 08:31:42.373', '2024-03-28 08:30:42.373')

Returns 1

Clearly not a Year between 12/31/2023 and 03/28/2024

Am I doing something wrong here?

Looking up the syntax tells us that the DATEDIFF function should accept a DATETIME format.

Thanks for your review and am hopeful for a reply.

SQL Server | SQL Server Transact-SQL
Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

SQL Server | Other
SQL Server | Other

Additional SQL Server features and topics not covered by specific categories

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. LiHongMSFT-4306 31,621 Reputation points
    2024-03-29T02:13:05.4266667+00:00

    Hi @Bobby P

    As documented here: DATEDIFF (Transact-SQL)

    This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

    Therefore, the result is right.

    If you want decimal results in years, try this: 

    SELECT CAST(DATEDIFF(D,'2023-12-31 08:31:42.373', '2024-03-28 08:30:42.373')/365.25 AS DECIMAL(10, 2))

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Was this answer helpful?

    0 comments
  2. Erland Sommarskog 134.3K Reputation points MVP Volunteer Moderator
    2024-03-28T22:18:01.4133333+00:00

    The result is correct. The Learn page for DATEDIFF says this:

    This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

    (Emphasis added on boundaries.)

    Thus, both these reutrn 1:

    SELECT datediff(YEAR, '20231231', '20240101'), 
       datediff(YEAR, '20230101', '20241231')

    Was this answer helpful?

    0 comments
  3. Olaf Helper 47,616 Reputation points
    2024-03-28T13:20:03.25+00:00

    Am I doing something wrong here?

    Yes, the result is correct, 2024 - 2023 = 1 year difference.

    Better use month or days for the query.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.