DateDiff function adding an extra year for the customer dob

Question

How can I alter the SQL query to not return a 3 for customer Mike since he is not 3 years old yet. As you can see the customer turns 3 in December of 2021. But my query is giving him the age of 3. Is there anyways to alter or make a query that gives him the correct age?

SELECT 
    id, 
    name, 
    dob, 
    DATEDIFF(YYYY, dob, GETDATE())
FROM 
    customer
WHERE
    DATEDIFF(YYYY, dob, GETDATE()) >= 2 
    AND DATEDIFF(YYYY, dob, GETDATE()) <= 4

Results:

id name    dob         datediff
-------------------------------
1  Mike    2018-12-05      3

This is the way `DATEDIFF` works - if you tell it to use `YYYY` , then it **will** only look at the year - and 2021 (for current year) minus 2018 **is** 3 - so it's just your expectations that are wrong - not SQL Server's inner workings..... — marc_s, Aug 12 '21 at 19:23
you just need to work with more granular units eg days. Also note your query as written is not *sargable* and will not perform well. — Stu, Aug 12 '21 at 19:57

SteveC · Answer 1 · 2021-08-12T19:39:52.187

There are many varied solutions to this issue on SQL Server. This answer is based on a Stack Overflow answer from a question where the accepted answer is not the best answer imo. When it comes to calculating "age from birth" there are advantages to using date format 112. You could try calculating the customer age something like this

declare 
  @as_of        datetime=getdate(),
  @bday         datetime='2018-12-05';

select 
    (0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112))/10000 age;

age
2

MatBailie · Answer 2 · 2021-08-22T23:35:30.247

If you want an integer number of years (no fractional part)...

SELECT
  *,
  CASE
    WHEN DATEADD(year, diff.years, customer.dob) > GETDATE()
    THEN diff.years - 1
    ELSE diff.years
  END
    AS age
FROM
  customer
CROSS APPLY
(
  SELECT DATEDIFF(year, customer.dob, GETDATE()) AS years
)
  AS diff
WHERE
      customer.dob >  DATEADD(year, -5, CAST(GETDATE() AS DATE)) -- Born more recently than 5 years ago, so at most 4 years 11 month and 30 days old
  AND customer.dob <= DATEADD(year, -2, CAST(GETDATE() AS DATE)) -- Born two years ago or earlier, so at least 2 years and 0 days old

The cross apply is just so that I can write the DATEDIFF() once and refer to it as many times as I like (Don't Repeat Yourself).

But then I also refactored the where clause. By moving the calculation to be on GETDATE() rather than on the dob column, I both make the maths simpler (than the case statement), but also make it so that any index on dob can be used (SARGable).

CAST(GETDATE() AS DATE) just removes the time part of today's date, on the assumption your dob (and calculations) don't account for the exact time they were born ;)

DateDiff function adding an extra year for the customer dob

2 Answers2