CODEWITHSUNDEEP
sql
Celbis H.
Celbis H.

Reputation: 1

How to add condition into new column that just added? -SQL-

Query is below

select fname,lname,DATEDIFF(YY,birthdate,GETDATE()) as age from EMPLOYEE where age = 72

but there is an error says --> "Invalid column name 'age'.

How can i solve it? Thank you!

Upvotes: 0

Views: 30

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

My favorite way to do this in SQL Server uses a lateral join:

select e.fname, e.lname, v.age
from EMPLOYEE e cross apply
     (values (DATEDIFF(year, birthdate, GETDATE())) v(age)
where v.age = 72;

Notes:

  • The more traditional ways of solving this use CTEs, subqueries, or repeat the expression.
  • More importantly, you have a very arcane definition of age. It is now 2017-04-05 where I am. My your arithmetic, anyone born in 2016 would have an age of "1".

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

In SQL Server you have to repeat the expression (from the select) to use it in the where clause:

select fname,lname,DATEDIFF(Year,birthdate,GETDATE()) as age 
from EMPLOYEE 
where DATEDIFF(Year,birthdate,GETDATE()) = 72

Unless using something like cross apply() to create the calculated field.

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

Just repeat the expression in the where clause:

select fname,
    lname,
    DATEDIFF(YY, birthdate, GETDATE()) as age
from EMPLOYEE
where DATEDIFF(YY, birthdate, GETDATE()) = 72

Upvotes: 0

Related Questions