Calculating age in where clause-CodePudding

In the users table I have birth_date column. I want to select only users under the age 18.

I tried using alias

select 
    *, 
    age = case
              when datediff(year, getdate(), birth_date) > 0 
                  then year(getdate()) - year(birth_date) - 1 
                  else year(getdate()) - year(birth_date)
          end 
from 
    users
where 
    age < 18

But apparently I cannot use alias in where.

So I tried using case but it's also not gonna work

select * 
from users 
where 
    case 
        when datediff(year, getdate(), birth_date) > 0 
            then year(getdate()) - year(birth_date) - 1 < 18
            else year(getdate()) - year(birth_date) < 18

What shall I do in this case? I don't want to use a stored procedure.

CodePudding user response：

Use cte

WITH cte AS
(
  SELECT *,
      CASE
        WHEN DATEDIFF(year, getdate(), birth_date) > 0 
          THEN year(getdate()) - year(birth_date) - 1
        ELSE year(getdate()) - year(birth_date) 
      END AS age
  FROM users 
)
SELECT *
FROM cte
WHERE age < 18

demo in db<>fiddle

CodePudding user response：

simple use this !

SELECT * FROM dbo.users WHERE DATEDIFF(day,birth_date,GETDATE()) < 6570

CodePudding user response：

I think you're just looking for direction on how to use an alias/CASE statement in a column?

If that's correct, then you'll just need to surround your alias with brackets.

select *
      ,case
           when datediff(year, getdate(), birth_date) > 0 
           then year(getdate()) - year(birth_date) - 1 
           else year(getdate()) - year(birth_date)
       end as age
from users
where age < 18

CodePudding user response：

Below is one way to calculate age which accounts for leap days and other complexities. This calculates the difference of the yyyymmdd integer values and then divides by 10000 to evaluate only the year difference.

SELECT *
FROM users
WHERE
    (CAST(FORMAT(GETDATE(), 'yyyyMMdd') AS int) -
     CAST(FORMAT(birth_date, 'yyyyMMdd') AS int)) / 10000 < 18;