Birthdate > 21 years old - 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 2000 Forums

SQL Server Development (2000)

Birthdate > 21 years old

Author

Topic

db_sysadmin
Starting Member

27 Posts

Posted - 2006-04-05 : 10:57:29

Hey guys,

I have this issue: What's the best way, or correct way to ask for a certain BirthDate Field if that person is under 21 years old?

I used this sentence, but it works only for years:

DATEDIFF(yyyy, BirthdateField, GETDATE()) < 21

Thanks a lot!!!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-04-05 : 11:07:41

Maybe:


IF Dateadd(yyyy,21,BirthdateField) > dateadd(dd, datediff(dd,0,getdate()), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 11:09:08

This will help you
http://sqlteam.com/forums/topic.asp?TOPIC_ID=62729

Madhivanan

Failing to plan is Planning to fail

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-04-05 : 12:26:35

This should also help you determine someone's age:

DECLARE @birthdate datetime
SET @birthdate = CONVERT(datetime, '19670405')

SELECT DATEDIFF(year, @birthdate, getdate()) - ( (DATEPART(dy, @birthdate) -1) / DATEPART(dy, getdate()) )

db_sysadmin
Starting Member

27 Posts

Posted - 2006-04-05 : 13:04:54

Thanks a Lot Guys!! All solutions have been so helpful!

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-04-05 : 20:27:48

Are you building a SQL based beer dispenser?

If so, I am available for QA contract

Nathan Skerl

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-05 : 23:13:09

quote:
Originally posted by nosepicker

This should also help you determine someone's age:

DECLARE @birthdate datetime
SET @birthdate = CONVERT(datetime, '19670405')

SELECT DATEDIFF(year, @birthdate, getdate()) - ( (DATEPART(dy, @birthdate) -1) / DATEPART(dy, getdate()) )

The two methods shown disagree on the way they handle birthdays on February 29.

The F_AGE_YYYY_MM_DD function will make February 28 the birthday in non-leap years.

The method posted by nosepicker will make March 1 the birthday in non-leap years.



select 
	[Age 1] =
		DATEDIFF(year, FR, TD) - 
		( (DATEPART(dy, FR) -1) / DATEPART(dy, TD) ) ,
	[Age 2] =
		dbo.F_AGE_YYYY_MM_DD( FR, TD ),
	FR = convert(varchar(10),FR,121),
	TD = convert(varchar(10),TD,121)
from
	(
	select FR = convert(datetiMe,'19670404'), TD = getdate() union all
	select FR = convert(datetiMe,'19670405'), TD = getdate() union all
	select FR = convert(datetiMe,'19670406'), TD = getdate() union all
	select FR = convert(datetiMe,'20000229'), TD = '20030228' union all
	select FR = convert(datetiMe,'20000229'), TD = '20030301' union all
	select FR = convert(datetiMe,'20000229'), TD = '20040228' union all
	select FR = convert(datetiMe,'20000229'), TD = '20040229' 
	) a

Results:


Age 1       Age 2      FR         TD         
----------- ---------- ---------- ---------- 
39          0039 00 01 1967-04-04 2006-04-05
39          0039 00 00 1967-04-05 2006-04-05
38          0038 11 30 1967-04-06 2006-04-05
2           0003 00 00 2000-02-29 2003-02-28  Disagreement 
3           0003 00 01 2000-02-29 2003-03-01
3           0003 11 31 2000-02-29 2004-02-28
4           0004 00 00 2000-02-29 2004-02-29

(7 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources