Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Age Expand / Collapse
Author
Message
Posted Wednesday, May 26, 2010 8:36 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:41 PM
Points: 29, Visits: 212
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
Posted Wednesday, May 26, 2010 8:53 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
You can use DATEDIFF function:

LIke:

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

Post #928708
Posted Wednesday, May 26, 2010 9:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 6,544, Visits: 8,759
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
Posted Wednesday, May 26, 2010 9:57 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
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!
Post #928720
Posted Friday, May 28, 2010 9:00 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 01, 2013 8:49 AM
Points: 79, Visits: 1,328
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
Posted Friday, May 28, 2010 9:21 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:02 PM
Points: 22,492, Visits: 30,190
You can also check out this article and the discussion that followed.



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
Posted Friday, May 28, 2010 9:26 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:04 PM
Points: 32,779, Visits: 14,939
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
Posted Tuesday, September 25, 2012 12:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:06 AM
Points: 10, Visits: 72
Wayne,

This doesn't work on leap years.

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

mitch
Post #1364218
Posted Tuesday, September 25, 2012 1:02 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 3,081, Visits: 11,230
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
Posted Tuesday, September 25, 2012 1:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:34 AM
Points: 35,955, Visits: 30,246
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1364267
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse