[solved] Determining age from birth date -- ACCESS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[solved] Determining age from birth date -- ACCESS

Author

Topic

mallorz
Starting Member

11 Posts

Posted - 2012-09-04 : 21:22:49

Hello,

I am trying to make a query from the first/last names of employees who are over 21. The actual extraction here isn't a problem, it's getting the correct data. The code I have tried is below, but it returns 70 of my 71 "employees" and should only show 69 (assuming I counted that right).

SELECT emp_fname, emp_lname
FROM Employee
WHERE (DATE() - birth_date >= 21;

Any ideas what I am doing wrong here? I am not entirely sure how to get the age when all I am given is there birthdate in shortdate format (12/4/1987).

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-05 : 01:04:35

where birth_date<=dateadd("yyyy",-21,date())

arpana patil
Starting Member

24 Posts

Posted - 2012-09-05 : 01:25:13

where (DATEPART(year,GETDATE())-DATEPART(year,'1987/4/12'))>=21

Replace 1987/4/12 with the respective column name

xloafery
Starting Member

5 Posts

Posted - 2012-09-05 : 09:20:29

SELECT emp_fname, emp_lname
FROM Employee
where datediff(yy, birth_date, getdate()) >= '21'

keep in mind that only gives information accurate to the year.

SELECT emp_fname, emp_lname
FROM Employee
where datediff(mm, birth_date, getdate()) >= 252

Would give information to the month, just switch for day if it has to be exact.

mallorz
Starting Member

11 Posts

Posted - 2012-09-05 : 12:23:06

quote:
Originally posted by xloafery

SELECT emp_fname, emp_lname
FROM Employee
where datediff(mm, birth_date, getdate()) >= 252

What does the number 252 signify?

Thanks for all the tips, hopefully I'll get a chance to try this out tonight or tomorrow.

xloafery
Starting Member

5 Posts

Posted - 2012-09-06 : 03:23:52

252 is 21 years in months (21*12) and would give slightly better accuracy than just checking the year part.

Might not be needed if you just want to know how many whole years it was since people were born :)

mallorz
Starting Member

11 Posts

Posted - 2012-09-06 : 20:49:23

Okay, I finally had a chance to type all of these in.
I get the same error with each one:
Undefined function 'GETDATE' in function.

Is there an easier way to do this? These expressions look much more confusing than I expected (only being on week 2 of sql).

Also, to clarify, I would assume they want the age 21 to the day. This is the last query of this assignment and it is driving me nuts! :-/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-07 : 11:39:41

getdate() is a ms sql function, and as you are using ms access, you should use the function date().

ps.: what error did you get, trying my earlier suggestion? you couldn't possibly get "Undefined function 'GETDATE' in function", as I didn't use getdate().

mallorz
Starting Member

11 Posts

Posted - 2012-09-07 : 18:35:00

quote:
Originally posted by bitsmed

getdate() is a ms sql function, and as you are using ms access, you should use the function date().

ps.: what error did you get, trying my earlier suggestion? you couldn't possibly get "Undefined function 'GETDATE' in function", as I didn't use getdate().

I must have skipped yours! I'm sorry. It worked perfectly. Thanks for the help and the clarification of GETDATE/DATE(). It is very much appreciated :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources