Calculation Date of Birth - 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

General SQL Server Forums

New to SQL Server Programming

Calculation Date of Birth

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-08-13 : 11:51:28

I need to figure out how to calculate age from date of birth. How would I do that?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-13 : 11:53:22

see section "Finding Age" in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 11:57:52

[code]CREATE FUNCTION GetAge
(
@DOB datetime
)
RETURNS int
AS
BEGIN
DECLARE @Age int
SELECT @Age=CASE WHEN DAY(@DOB)>DAY(GETDATE())
OR MONTH(@DOB)>MONTH(GETDATE())
THEN DATEDIFF(yy,@DOB,GETDATE()) -1
ELSE DATEDIFF(yy,@DOB,GETDATE())
END
RETURN @Age
END
[/code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-13 : 12:11:58

This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-08-13 : 12:14:55

quote:

Originally posted by visakh16

CREATE FUNCTION GetAge
(
@DOB datetime
)
RETURNS int
AS
BEGIN
DECLARE @Age int
SELECT @Age=CASE WHEN DAY(@DOB)>DAY(GETDATE()) 
         OR MONTH(@DOB)>MONTH(GETDATE())
            THEN DATEDIFF(yy,@DOB,GETDATE()) -1
            ELSE DATEDIFF(yy,@DOB,GETDATE())
        END
RETURN @Age
END

GETDATE() cannot be used in a function in SQL Server 2000, if that is what they are running.

Also, if someone is born on Feb 29, most people consider they are a year older on Feb 28 in non-leap years, but on Feb 29 in leap years.

CODO ERGO SUM

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-08-13 : 13:05:59

Thanks but what if I didn't want to use it as a function?

Can I do something like this? How could I do it this way?
(getdate())-(clm_14)/365.25,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:17:13

quote:
Originally posted by werhardt

Thanks but what if I didn't want to use it as a function?

Can I do something like this? How could I do it this way?
(getdate())-(clm_14)/365.25,

just use logic inside function directly then.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources