Age and SQL date fields - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Age and SQL date fields

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-11 : 06:43:19

I have an SQL DB setup and the users field stores their date of birth in a datetime field.

I have a form where I'm querying the db from and there is an "Age" field and a date field.

So you could put "10" in the age field and "01/12/1999" in the date field.

I want the db to return all the records from that table where the user will be 10 on that given date.

I can do it so it returns all users who's birthday is in that year, but I need to make it so that it only returns records where they are that age on that given date.

So for example, I have 2 people, Ben and Jerry. Ben's dob is 01/12/1999 and Jerry's is 05/12/1999.

If I ran the above query and entered 10 in the age and 04/12/1999 in the date field, it should only return Ben's record, because Jerry is 10 on 5th and therefore not 10 yet.

Does that make sense?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 06:48:52

See http://weblogs.sqlteam.com/peterl/archive/2009/02/13/Improved-anniversary-calculation-better-datediff.aspx

E 12°55'05.63"
N 56°04'39.26"

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-11 : 09:08:04

How would I use that for what I need?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 09:35:19

You can also use this function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80709

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-03-11 : 09:42:41

With Peso's function:

DECLARE 
  @myDate datetime,
  @Age int

DECLARE @table table (
  Firstname varchar(20),
  DOB datetime
)

INSERT INTO @table 
SELECT 'Ben', '1999-12-01' UNION ALL SELECT 'Jerry', '1999-12-05'

SELECT @myDate = '2009-12-04', @Age = 10

SELECT 
  *,
  Age = dbo.fnYearsApart(DOB, @myDate)
FROM @table
WHERE dbo.fnYearsApart(DOB, @myDate) = @Age

- Lumbago

bpsintl
Posting Yak Master

132 Posts

Posted - 2009-03-11 : 12:05:13

Lumbago, thanks, that's done the trick!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources