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

What do you need the variable for? You can just add a computed column with the formula `getdate() - emp.birthdate` in your select list — shree.pat18, Nov 26 '15 at 05:35

score 1 · Answer 1 · answered Nov 26 '15 at 05:36

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')

score 0 · Answer 2 · edited Nov 26 '15 at 06:36

0

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

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

edited Nov 26 '15 at 06:36

Ajay2707

5,690
6
40
58

answered Nov 26 '15 at 05:38

Poonam

669
4
14

It's my first time to use DATEDIFF(), and it looks like i'll be using this more often, thanks! – A.Arc Nov 26 '15 at 08:19

score 0 · Answer 3 · edited May 23 '17 at 12:15

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

score 0 · Answer 4 · answered Nov 26 '15 at 05:46

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

score 0 · Accepted Answer · answered Nov 26 '15 at 05:46

0

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

answered Nov 26 '15 at 05:46

Paresh J

2,401
3
24
31

Thanks! This is really helpful! – A.Arc Nov 26 '15 at 08:14

Declaring a variable with a value from select query

5 Answers5