Declaring a variable with a value from select query

Question

DECLARE @age DATETIME
SET @age = (GETDATE() - emp.Birthdate)
SELECT 
 emp.BusinessEntityID, emp.BirthDate, @age
FROM 
 HumanResources.Employee AS emp
WHERE 
 emp.OrganizationLevel > = 3 
 AND ((GETDATE() - emp.Birthdate) BETWEEN '1930-01-01 00:00:00.000' AND '1940-01-01 00:00:00.000')

As you pros can see, this will not work, I'm hoping to display the ages of the people who are aged 30-40 with their id, bday and age. declaring the @age is my problem. I tried using substring(getdate...), 2, 2) but it doesn't seem to work. Any ideas? Thanks

• shree.pat18: What do you need the variable for? You can just add a computed column with the formula getdate() - emp.birthdate in your select list

Answer 1

At the moment you set the value to @age,

SET @age = (GETDATE() - emp.Birthdate)

there is no emp.

You can simply do the following query:

SELECT emp.BusinessEntityID, emp.BirthDate, (GETDATE() - emp.Birthdate) AS Age
FROM HumanResources.Employee AS emp
WHERE emp.OrganizationLevel > = 3 AND
 (GETDATE() - emp.Birthdate) BETWEEN '1930-01-01 00:00:00.000' AND '1940-01-01 00:00:00.000')

Answer 2

No need to use variable in the query. You can do this simply as follow:

SELECT t.BusinessEntityID, t.BirthDate, t.Age
FROM
(SELECT emp.BusinessEntityID, emp.BirthDate, CAST(datediff(DAY, emp.Birthdate, GETDATE()) / (365.23076923074) as int) as 'Age'
FROM HumanResources.Employee AS emp
WHERE emp.OrganizationLevel > = 3) t
WHERE t.Age >= 30 and t.Age <=40

• A.Arc: Thanks! This is really helpful!

Answer 3

First, you can't set a scalar variable with more then one value. Second, for this purpose you don't need a variable at all. you can simply do this:

SELECT BusinessEntityID, BirthDate, DATEDIFF(Year, BirthDate, GETDATE()) As Age
FROM HumanResources.Employee 
WHERE OrganizationLevel > = 3 
AND DATEDIFF(Year, BirthDate, GETDATE()) BETWEEN 30 AND 40

Note: This will not give an exact age. To get the exact age you can use the answers in this post. I think the best answer there is this answer by dotjoe

Answer 4

I think you're Trying to get Employees between 30 and 40 years old you can simply use DateDiff Function to do this as the following Query:

SELECT BusinessEntityID,BirthDate,DATEDIFF(YEAR,BirthDate,GETDATE()) AS Age 
FROM HumanResources.Employee 
WHERE DATEDIFF(YEAR,BirthDate,GETDATE()) BETWEEN 30 AND 40 
ORDER BY Age ASC 

Answer 5

You can use Datediff function to find exact difference between 2 dates

SELECT 
 datediff(yyyy,GETDATE(),date)
FROM 
 [PRGX_AS_SandBox].[dbo].[Test_SO]

• A.Arc: It's my first time to use DATEDIFF(), and it looks like i'll be using this more often, thanks!