Age

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Age

15 posts, Page 1 of 2

1 2 »»

Age

Rate Topic

Display Mode

Topic Options

Author

Message

QQ-485619

Posted Wednesday, May 26, 2010 8:36 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, January 19, 2013 9:54 PM
Points: 27, Visits: 193

I have a table called member which contains memberid and DOB. How can I calculate age to date based on Date of Birth.

Thanks,

Post #928705

ColdCoffee

Posted Wednesday, May 26, 2010 8:53 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

You can use DATEDIFF function:

LIke:

SET DATEFORMAT DMY
SELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE

Post #928708

WayneS

Posted Wednesday, May 26, 2010 9:35 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:22 PM
Points: 6,367, Visits: 8,225

ColdCoffee (5/26/2010)

You can use DATEDIFF function:

LIke:

SET DATEFORMAT DMY
SELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE

Since DateDiff returns the number of boundaries crossed in comparing these two dates, it will report the wrong age if today's date is not >= your DOB. So, use this instead:

SELECT CASE WHEN datepart(dayofyear, GetDate() ) >= datepart(dayofyear, @DOB)
            THEN DATEDIFF(yy,@DOB,GETDATE()) 
            ELSE DATEDIFF(yy,@DOB,GETDATE()) -1 
       END AGE

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #928714

ColdCoffee

Posted Wednesday, May 26, 2010 9:57 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

WayneS (5/26/2010)

ColdCoffee (5/26/2010)

You can use DATEDIFF function:

LIke:

SET DATEFORMAT DMY
SELECT DATEDIFF(yy,'21-03-1985',GETDATE()) AGE

Since DateDiff returns the number of boundaries crossed in comparing these two dates, it will report the wrong age if today's date is not >= your DOB. So, use this instead:

SELECT CASE WHEN datepart(dayofyear, GetDate() ) >= datepart(dayofyear, @DOB)
            THEN DATEDIFF(yy,@DOB,GETDATE()) 
            ELSE DATEDIFF(yy,@DOB,GETDATE()) -1 
       END AGE

That perfectly makes sense!
How sweet it is to start the day with learning a titbit.. wow
Thanks Wayne Shef! Smile

Post #928720

Adam Gojdas

Posted Friday, May 28, 2010 9:00 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, March 22, 2013 3:37 PM
Points: 79, Visits: 1,317

Another way to calculate the age is:

SELECT (ABS(CAST(CONVERT(char(8),GETDATE(),112) AS int)
      - CAST(CONVERT(char(8),@DOB,112) AS int)) / 10000) [AGE];

Here is a link to where I originally saw this type of calculation. It is in the comments section. :
http://www.sqlmag.com/article/tsql3/datetime-calculations-part-5.aspx

Post #929742

Lynn Pettis

Posted Friday, May 28, 2010 9:21 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,588, Visits: 27,375

You can also check out this article and the discussion that followed.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #929761

Steve Jones - SSC Editor

Posted Friday, May 28, 2010 9:26 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:47 PM
Points: 31,406, Visits: 13,722

I'd second Lynn's article. It's a good discussion of this topic.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #929767

mitch 14804

Posted Tuesday, September 25, 2012 12:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 12:39 PM
Points: 9, Visits: 50

Wayne,

This doesn't work on leap years.

Try these dates:
@DOB = '09/26/2011'
GetDate() = '09/25/2012'

mitch

Post #1364218

Michael Valentine Jones

Posted Tuesday, September 25, 2012 1:02 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481

You can use the function on the link below to find the age:
Age Function F_AGE_IN_YEARS
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462

Post #1364242

Jeff Moden

Posted Tuesday, September 25, 2012 1:39 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:36 AM
Points: 32,890, Visits: 26,759

Adam Gojdas (5/28/2010)

Another way to calculate the age is:

SELECT (ABS(CAST(CONVERT(char(8),GETDATE(),112) AS int)
      - CAST(CONVERT(char(8),@DOB,112) AS int)) / 10000) [AGE];

Here is a link to where I originally saw this type of calculation. It is in the comments section. :
http://www.sqlmag.com/article/tsql3/datetime-calculations-part-5.aspx

Careful now... Because of the date conversions to the CHAR datatype, that will be relatively slow.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1364267

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions