DATEDIFF (YEAR, StartDate, EndDate) returns incorrectly

Question

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.

Answer 1

Am I doing something wrong here?

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

Better use month or days for the query.

Answer 2

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

Answer 3

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".