CODEWITHSUNDEEP
sqlsql-serverroundingdatediff
Bernard Polman
Bernard Polman

Reputation: 855

SQL Server DATEDIFF round up the YEAR difference. How to round it down?

I have a problem that I tried googling but most questions are about rounding down hours or minutes.

I'm checking birthday dates for users and I have two dates that are 99.3 years apart. That means that the user is 99 years old but this piece of code:

DATEDIFF(YEAR, r.BirthDate, ISNULL(@Date,GETDATE()))

returns a value of 100. Is there a way to round the value down?

Upvotes: 2

Views: 11564

Answers (8)

Slava Murygin
Slava Murygin

Reputation: 1955

It looks like DATEDIFF rounds not only years or days, same with hours, minutes etc. Not sure about the reason behind. If there is no reason, then shame on MS. Instead of using IIF or CASE - the best suggestions were to use "smaller intervals". For years I'd use rather days than weeks or months. Months are too large and for weeks you have to use a "strange" divider:

DECLARE @BD TABLE (birthdate Date, [GetDate] Date)
INSERT INTO @BD VALUES 
('2025-01-29','2045-01-29'),
('2025-01-29','2045-01-28'),
('2025-02-28','2045-02-28'),
('2025-02-28','2045-02-27'),
('2025-02-27','2045-02-28'),
('2025-03-01','2045-03-01'),
('2025-03-29','2045-03-18'),
('2024-01-29','2044-01-29'),
('2024-01-29','2044-01-28'),
('2024-02-29','2044-02-29'),
('2024-02-29','2044-02-28'),
('2024-02-28','2044-02-28'),
('2024-02-28','2044-02-29'),
('2024-03-01','2044-03-01'),
('2024-03-01','2044-02-29'),
('2024-03-01','2044-02-28'),
('2024-03-29','2044-03-18');
SELECT birthdate,[GetDate]
    , Age = IIF(MONTH(birthdate)=MONTH([GetDate]) and DAY(birthdate)=DAY([GetDate])
            , DATEDIFF(YEAR,birthdate,[GetDate])
            , FLOOR( (DATEDIFF(DAY,birthdate,[GetDate])) / 365.25))
FROM @BD

birthdate   GetDate     Age
2025-01-29  2045-01-29  20
2025-01-29  2045-01-28  19
2025-02-28  2045-02-28  20
2025-02-28  2045-02-27  19
2025-02-27  2045-02-28  20
2025-03-01  2045-03-01  20
2025-03-29  2045-03-18  19
2024-01-29  2044-01-29  20
2024-01-29  2044-01-28  19
2024-02-29  2044-02-29  20
2024-02-29  2044-02-28  19
2024-02-28  2044-02-28  20
2024-02-28  2044-02-29  20
2024-03-01  2044-03-01  20
2024-03-01  2044-02-29  19
2024-03-01  2044-02-28  19
2024-03-29  2044-03-18  19

Upvotes: 0

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153710

This is how fixed it.

Instead of:

DATEDIFF(YEAR, r.BirthDate, ISNULL(@Date,GETDATE()))

use this:

FLOOR(DATEDIFF(week, r.BirthDate, ISNULL(@Date,GETDATE())) / 52.177457)

Here's an article that demonstrates that this trick works like a charm.

Upvotes: 2

Adam Smith
Adam Smith

Reputation: 3

In Snowflake:

ROUND(DATEDIFF('month', "dateOfBirth", CURRENT_DATE())/12)

You have to do it by months as if you pick years it seems to round up.

Upvotes: 0

d.popov
d.popov

Reputation: 4255

Another way to solve this (without calculating the date difference 3 times or more) is to get the total number of years when subtracting the two values:

SELECT datediff(YEAR, '1900', DATEADD(d, -1, GETDATE()) - r.BirthDate)

we subtract 1 day from the current date as the other day is '1/1/1900', which adds one day to the interval.

Upvotes: 0

Tony Phan
Tony Phan

Reputation: 11

The accepted solution by Gordon is good but the logic is reversed? The general idea is to see if the current month + day is greater than the birthdate's month + day and, if not, then subtract a year from the difference in years, because it hasn't reached or surpassed their birthdate yet.

SELECT YEAR(GETDATE()) - YEAR(birthdate) 
- IIF(MONTH(GETDATE()) * 100 + DAY(GETDATE()) >= MONTH(birthdate) * 100 + DAY(birthdate), 0, 1)

Upvotes: 1

Cato
Cato

Reputation: 3701

fin the difference in whole years (year - birth year) This is always the age the person reaches in that year. Knock a year off if it is earlier than their birthday.

Never be tempted to use day difference divided by 365.25 or 365 - for all sorts of reasons, the age can end up coming out wrong at certain dates, sometimes around leap-years

SELECT YEAR(getdate()) - YEAR(birthdate)  
       - CASE WHEN MONTH(getdate()) < MONTH(birthdate) 
               OR (DAY(getdate()) < DAY(birthdate) AND MONTH(getdate()) = MONTH(birthdate) )
            THEN
                1
            ELSE
                0
            END

Upvotes: 2

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution using MONTH instead of YEAR:

DECLARE @currdate AS DATE = '2019-02-08'

-- using YEAR
SELECT DATEDIFF(YEAR, '2000-05-01', ISNULL(@currdate, GETDATE())) -- 19

-- using MONTH
SELECT DATEDIFF(MONTH, '2000-05-01', ISNULL(@currdate, GETDATE())) / 12 -- 18

You can also ROUND up and down:

DECLARE @date DATE = '2019-02-08'

SELECT CAST(ROUND(DATEDIFF(MONTH, '1999-08-01', ISNULL(@date, GETDATE())) / 12.0, 0) AS INT) 
-- 20 (19.5)

SELECT CAST(ROUND(DATEDIFF(MONTH, '1999-09-01', ISNULL(@date, GETDATE())) / 12.0, 0) AS INT) 
-- 19 (19.4)

is there a way to round down days?

You can use days to round for days but this isn't more precise in some cases. You can check if the current date passed the day of birth or not (as @Cato mentioned in comments). In case the day of birth is greater than the current day (in same month) you can subtract a month.

DECLARE @birthday AS DATE = '2000-10-25'
DECLARE @currdate AS DATE = '2100-10-24'
SELECT ((DATEDIFF(MONTH, @birthday, ISNULL(@currdate, GETDATE())) - (CASE WHEN MONTH(@birthday) = MONTH(@currdate) AND DAY(@birthday) > DAY(@currdate) THEN 1 ELSE 0 END)) / 12)
-- 99

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use this logic to get the correct age:

select (case when month(birthdate) * 100 + day(birthdate) >=
                  month(getdate()) * 100 + day(getdate())
             then year(getdate()) - year(birthdate)
             else year(getdate()) - year(birthdate) - 1
        end) as age

This should be accurate, even in the presence of leap years. Basically it looks at the month-day portion of the birthdate and checks if it is on or later than today. The logic uses this information to determine the age in year.

Upvotes: 2

Related Questions