SQLTeam.com | Weblogs | Forums

Case when then dateadd getdate issue


#1

Morning folks,

I've a field with a date of birth in it.
Displayed as YYYY-MM-DD
Ex: 1990-08-19

I would like to use CASE, WHEN, THEN, END AS for this.

CASE Person.dob
WHEN <= DATEADD(YEAR, -26, GETDATE()) AND >= DATEADD(YEAR, -35, GETDATE()) THEN 'Age 26 to 35'
WHEN <= DATEADD(YEAR, -36, GETDATE()) AND >= DATEADD(YEAR, -45, GETDATE()) THEN 'Age 36 to 45'
WHEN <= DATEADD(YEAR, -46, GETDATE()) AND >= DATEADD(YEAR, -55, GETDATE()) THEN 'Age 46 to 55'
WHEN <= DATEADD(YEAR, -56, GETDATE()) AND >= DATEADD(YEAR, -65, GETDATE()) THEN 'Age 56 to 65'
WHEN <= DATEADD(YEAR, -66, GETDATE()) THEN 'Age 66+'
END AS 'Age'

When executed, the column shows something like the following

Age
Age 36 to 45
Age 26 to 35
Age 36 to 45
Age 66+
Age 66+
Age 56 to 65

Where have I gone awry?

Thank you


#2

Much simpler to just compute the actual age and then convert it to a range. I'll use a CROSS APPLY to calc the age to assign an alias name to the result.

SELECT ...,
    CASE WHEN Person_Age >= 66 THEN 'Age 66+'
         WHEN Person_Age >= 56 THEN 'Age 56 to 65'
         WHEN Person_Age >= 46 THEN 'Age 46 to 55'
         WHEN Person_Age >= 36 THEN 'Age 36 to 45'
         WHEN Person_Age >= 26 THEN 'Age 26 to 35'
         WHEN Person_Age >= 18 THEN 'Age 18 to 25'
         ELSE 'Age <18'
    END AS 'Age'
FROM Person
CROSS APPLY (
    SELECT DATEDIFF(YEAR, Person.dob, GETDATE()) - 
        CASE WHEN CONVERT(char(5), Person.dob, 1) <
                  CONVERT(char(5), GETDATE(), 1) THEN 1 ELSE 0 
        END AS [Person_Age]
) AS ca1

#3

That is close, but not always accurate.
1970-05-14 comes back as Age 36-45, but they are actually 46

Any other solutions out there or is there a way to make this accurate to todays date?


#4

Check out https://www.mssqltips.com/sqlservertip/2688/work-around-for-calculating-age-using-the-sql-server-datediff-function/


#5

You didn't post any data to test with, so I didn't run any final testing.

Change the "<" to ">" in the CROSS APPLY.

You're welcome, btw. Good luck on future qs.


#6

Scott, that is perfect!
Thank you so much!!

If there is a next time, I will include data.