SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Calculating age in years
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

malaytech2008
Yak Posting Veteran

India
95 Posts

Posted - 11/11/2008 :  00:37:43  Show Profile  Reply with Quote
You can try to find the Date of birth and modify this if any flaw u find..

alter procedure Cal_DateofBirth_1 @dateofbirth datetime
as
begin
declare @EndDt datetime,
@DOB datetime
set @EndDt=getdate()
set @DOB=@dateofbirth
if @EndDt >=@DOB
select case when day(@enddt) < day(@dob)
then
case when ((month(@enddt)-1) >= month(@dob))
then (year(@enddt)- year(@dob))
else
(year(@enddt)- (year(@dob)+1))
end
else
case when (month(@enddt) >= month(@dob))
then (year(@enddt)- year(@dob))
else
(year(@enddt)- (year(@dob))+1)
end
end as years,

case when day(@enddt) < day(@dob)
then
case when ((month(@enddt)-1) >= month(@dob))
then ((month(@enddt)-1)- month(@dob))
else
(month(@enddt)- month(@dob))+12
end
else
case when (month(@enddt) >= month(@dob))
then (month(@enddt)- month(@dob))
else
(month(@enddt)- month(@dob))+12
end
end as months,
case when day(@enddt) >= day(@dob)
then (day(@enddt)-day(@dob))
else
case when month(@dob) in(1,3,5,7,8,10)
then
((day(@enddt)+30)-day(@dob))
when month(@dob) in(4,6,9,11,12)
then
((day(@enddt)+31)-day(@dob))
else
case when (year(@dob)%4=0 or year(@dob)%100=0)
then ((day(@enddt)+29)-day(@dob))
else ((day(@enddt)+28)-day(@dob))
end
end
end as days
else
print 'please provide valid date of birth'

end

malay
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/11/2008 :  04:33:32  Show Profile  Reply with Quote
quote:
Originally posted by Michael Valentine Jones
SQL Server seems to favor Feb 28:
select [One Year Birthday] = dateadd(year,1,'20040229')
Results:
One Year Birthday                                      
------------------------------------------------------ 
2005-02-28 00:00:00.000

(1 row(s) affected)




I think it's likely that's the case because someone decided that DATEADD(year, @n, @dt) should be equivalent to DATEADD(month, @n*12, @dt).
Go to Top of Page

sendmejunks
Starting Member

1 Posts

Posted - 05/17/2010 :  15:21:35  Show Profile  Reply with Quote
here's my version

declare @age int;
declare @m int;
set @age = 0;
set @m = datediff(month,@dob,@Dateagainst)
if datepart(day,@Dateagainst) < datepart(day,@dob)
set @m = @m - 1;
set @age = floor(@m/12) ;
if @age < 0 set @age = null;
return @age;
Go to Top of Page

plcrewe
Starting Member

1 Posts

Posted - 02/01/2011 :  11:16:43  Show Profile  Reply with Quote
To SELECT people's ages, I use the following SQL (NOTE: I've added the WHERE clause simply to show you how to view those people who are currently 39 years old. If you wish to view everybody then simply remove the WHERE clause from this statement).

SELECT surname, birth_date, DATEDIFF(yy, birth_date, GETDATE()) -
(CASE WHEN (DATEPART(m, birth_date) < DATEPART(m, GETDATE())) OR
(DATEPART(m, birth_date) = DATEPART(m, GETDATE()) AND
DATEPART(d, birth_date) <= DATEPART(d, GETDATE()))
THEN 0
ELSE 1
END) AS age
FROM people
WHERE DATEDIFF(yy, birth_date, GETDATE()) -
(CASE WHEN (DATEPART(m, birth_date) < DATEPART(m, GETDATE())) OR
(DATEPART(m, birth_date) = DATEPART(m, GETDATE()) AND
DATEPART(d, birth_date) <= DATEPART(d, GETDATE()))
THEN 0
ELSE 1
END) = 39
ORDER BY 3
Go to Top of Page

onecaring2001
Starting Member

1 Posts

Posted - 10/12/2011 :  19:48:10  Show Profile  Reply with Quote
/*

Just use this function and get the exact age in "YYYMMDD" format (YearsMonthsDays)



average days in a year: 365.2425

average days ion a month: 30.436875



USAGE: SELECT dbo.udf_CalcAge('1960-01-01', '2011-10-12')

RESULT: "0510911" -- 51 YEARS, 9 MONTHS AND 11 DAYS



*/



CREATE FUNCTION [dbo].[udf_CalcAge] (@StartDate datetime = NULL, @EndDate datetime = NULL)



RETURNS varchar(7)



WITH EXECUTE AS CALLER



AS

BEGIN



RETURN (

RIGHT('000' + CAST(FLOOR(DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) AS VARCHAR(3)), 3)+

RIGHT('00' + CAST(FLOOR((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) AS VARCHAR(2)), 2) +

RIGHT('00' + CAST(FLOOR(ROUND(((DATEDIFF(dd, @StartDate, @EndDate) / 365.2425) % 1 * 12) % 1 * 30.436875, 0)) AS VARCHAR(2)), 2)

)



END;
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000