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

Correct Age calculation Expand / Collapse
Author
Message
Posted Friday, January 6, 2012 2:52 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:54 AM
Points: 300, Visits: 499
We are using the following to calculate age:

It doesn't seem correct ?
Any ideas would be great?

Thanks
Joe

INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TAG_NAME = 'CUST_AGE', TAG_DATA = (SELECT DATEDIFF (yy,bdate, GETDATE())FROM client
WHERE Client.OID =@CLIENT_OID);
Post #1231756
Posted Friday, January 6, 2012 2:59 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
Hi

Try this post from my blog

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/12/08/age-calculation-with-sql-server.aspx




Clear Sky SQL
My Blog
Kent user group
Post #1231759
Posted Saturday, January 7, 2012 10:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, February 1, 2012 3:57 AM
Points: 91, Visits: 125
Here is good article for calculating age.

http://www.sqlservercentral.com/articles/T-SQL/63351/
Post #1231929
Posted Tuesday, January 10, 2012 9:00 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 9:54 AM
Points: 300, Visits: 499
Thanks,

That works great, but Im new at SQL and have tried but can't get the syntax right..
What if I wanted to select bdate from the "Client" file as date1 and getdate() as date2 ?

Thanks
Joe
Post #1233312
Posted Thursday, January 12, 2012 7:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 193, Visits: 1,199
This is how I do it...

select c.clientid, convert(char(10),c.dateofbirth,101) as DOB,
case
when ((MONTH(c.dateofbirth) * 100) + DAY(c.dateofbirth)) > ((MONTH(getdate()) * 100) + DAY(getdate()))
then DATEDIFF(year,c.dateofbirth,getdate()) - 1
else DATEDIFF(year,c.dateofbirth,getdate()) End As Age
from clients c
Post #1234806
Posted Tuesday, June 10, 2014 4:49 PM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
jbalbo (1/10/2012)
Thanks,

That works great, but Im new at SQL and have tried but can't get the syntax right..
What if I wanted to select bdate from the "Client" file as date1 and getdate() as date2 ?

Thanks
Joe


If I did it right, like this:


select
*, -- Actually list the columns you want returned
datediff(year, bdate, getdate()) -
case when dateadd(dd, datediff(dd, 0, getdate()), 0) < dateadd(year, datediff(year, bdate, getdate()), bdate)
then 1
else 0
end as Age
from
dbo.Client;





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 #1579443
Posted Tuesday, July 15, 2014 3:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:50 AM
Points: 1, Visits: 0
You can use this simple algorithm to calculate age.
Age = datediff(yy, date_of_birth, Today)

– (if Today < dateadd(yy, datediff(yy, date_of_birth, Today),date_of_birth)

then 1 else 0. If you need any kind writing help for your website or other helps then contact this.
Post #1592477
Posted Tuesday, July 15, 2014 8:10 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
randalltbartel (7/15/2014)
You can use this simple algorithm to calculate age.
Age = datediff(yy, date_of_birth, Today)

– (if Today < dateadd(yy, datediff(yy, date_of_birth, Today),date_of_birth)

then 1 else 0. If you need any kind writing help for your website or other helps then contact this.


And decoding my code into a simple algorithm, it matches what you posted.



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 #1592587
Posted Thursday, July 31, 2014 1:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 1:05 AM
Points: 2, Visits: 3
Thanks...for giving this useful Information.Its good to calculate the age.


SMO Company India
Post #1598115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse