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: Friday, August 1, 2014 12:53 PM
Points: 32, Visits: 228
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: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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: Saturday, September 13, 2014 8:51 AM
Points: 6,600, Visits: 8,900
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: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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: Wednesday, August 27, 2014 1:46 PM
Points: 79, Visits: 1,331
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: Yesterday @ 9:49 PM
Points: 23,396, Visits: 32,222
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 @ 7:12 AM
Points: 33,266, Visits: 15,432
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: Friday, June 13, 2014 1:45 PM
Points: 10, Visits: 75
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: Yesterday @ 5:25 PM
Points: 3,136, Visits: 11,490
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 @ 12:03 AM
Points: 37,065, Visits: 31,628
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."

(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