I was trying to calculate the age of a person in years, months and days as on present day. The input is the birth date while in yyyymmdd format. The age in yyyymmdd fromat is to be returned thru a select statement. Infact the date of births is already there in a table and you will have return the present age. Use of cursor is not prefered. Can anyone help me on this please. Aurobindo

Which database are you using ?

You can use a SELECT query rather than a cursor by substracting the birthdate from the system date. How you do that depends on the brand you're using, because vendors use different functions for determining the current date and for extracting parts of dates. For example, to retrieve the system date, DB2 uses CURRENT DATE, Oracle uses SYSDATE, SQL Server and Sybase use GETDATE.

Let us know which database software you're using and we'll try to tell you how you can structure the query.

For SQL SEVER GETDATE() returns the current date

You can then use the DATEDIFF Function to calculate the difference

for oracle, you may try this ......
i am sending it without testing , but i hope it will work with some modifications (if required at all!)

SELECT floor(months_between(sysdate,<column_name>)/12)||floor((months_between(sysdate,<column_name>) - floor(months_between(sysdate,<column_name>)/12)) from <table_name>

I just ran across this problem and was sure there would be a function out there to handle it, but couldn't find one. Just for the reusability of it, I wrote one for SQL 2005...

=======================================
How to calculate age in 2 easy steps…
=======================================
1. Run the following script in any SQL 2005 DB you want to determine Age in.
2. Calculate age anytime you want by sending a start date and end date in the format
select dbo.CalcAge(@StartDate, @EndDate)




The return value will be a decimal in the format of number of years old plus a decimal representing the number of days since the last anniversary date divided by the number of days until the next anniversary date – this does take into account leap years so be careful.



An example of how to break this information out as age in years and days since last anniversary is shown below (there is a precision issue, but ROUND seems to handle it). @NumDaysPerYear should be the number of days between last anniversary and next anniversary if you take into account leap year (365 or 366). The way to calculate @NumDaysPerYear is included in the function script.

select dbo.CalcAge(@StartDate, @EndDate) as AgeInYears, floor(dbo.CalcAge(@StartDate, @EndDate) ) AS AgeYear, cast(ROUND(((dbo.CalcAge(@StartDate, @EndDate) -floor(dbo.CalcAge(@StartDate, @EndDate) )) * @NumDaysPerYear),0) as smallint) As DaysSinceLastAnniversary



--===========================
-- RUN THIS SCRIPT TO
-- CREATE CalcAge Function
--============================

CREATE FUNCTION dbo.CalcAge (@StartDate datetime, @EndDate datetime)

RETURNS decimal(18,9)

WITH EXECUTE AS CALLER

AS

BEGIN

DECLARE @Age decimal(18,9), @RecentAnniversary smalldatetime, @UpcomingAnniversary smalldatetime,

@NumDaysPerYear int

--SELECT @StartDate='10/18/1910', @EndDate='8/11/2000'

-- DETERMINE THE MOST RECENT ANNIVERSARY DATE AND NEXT ANNIVERSARY DATE.

-- THIS WILL BE USED FOR DETERMINING THE DECIMAL PART OF AGE.

select @RecentAnniversary=cast(cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@EndDate) as varchar(4)) as smalldatetime)

if @RecentAnniversary>@EndDate

BEGIN

select @UpcomingAnniversary=@RecentAnniversary

select @RecentAnniversary=cast(cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@EndDate)-1 as varchar(4)) as smalldatetime)

END

else

select @UpcomingAnniversary=cast(cast(month(@StartDate) as varchar(2)) + '/' + cast(day(@StartDate) as varchar(2)) + '/' + cast(year(@EndDate)+1 as varchar(4)) as smalldatetime)

-- NOW DETERMINE THE NUMBER OF DAYS BETWEEN THE ANNIVERSARY DATES. T

-- THIS WILL TAKE INTO ACCOUNT LEAP YEARS

select @NumDaysPerYear = DateDiff(day, @RecentAnniversary, @UpcomingAnniversary)

-- GET THE BASE AGE OF OF THE DATES

SELECT @Age=DATEDIFF (YYYY, @StartDate, @EndDate) -

CASE

WHEN (MONTH(@StartDate)=MONTH(@EndDate) AND DAY(@StartDate) > DAY(@EndDate)

OR MONTH (@StartDate) > MONTH (@EndDate))

THEN 1

ELSE 0

END

-- NOW GET THE DECIMAL PART

--select @Age, DateDiff(day, @RecentAnniversary, @EndDate), @NumDaysPerYear

Select @Age=@Age + cast(DateDiff(day, @RecentAnniversary, @EndDate) as decimal(12,9)) / cast(@NumDaysPerYear as decimal(12,9))

-- TEST BREAKING OUT COMPONENTS. THERE ARE SOME PRECISION ISSUES, BUT THE ROUNDING

-- BELOW SEEMS TO ADDRESS IT

--select @Age as Age, floor(@Age) AS AgeYear, cast(ROUND(((@Age-floor(@Age)) * @NumDaysPerYear),0) as smallint) As DaysSinceLastAnniversary

RETURN (@Age)

END;

GO

-- ================
-- END SCRIPT
-- ================

--Marcel Crudele
--innerEcho, Inc.

create procedure pn_getDatedifference

(
@startdate datetime,
@enddate datetime
)
as
begin

declare @monthToShow int
declare @dayToShow int

--set @startdate='01/21/1934'
--set @enddate=getdate()

if(DAY(@startdate) > DAY(@enddate))
begin
set @dayToShow=0

if (month(@startdate) > month(@enddate))
begin
set @monthToShow= (12-month(@startdate)+ month(@enddate)-1)
end
else if (month(@startdate) < month(@enddate))
begin
set @monthToShow= ((month(@enddate)-month(@startdate))-1)
end
else
begin
set @monthToShow= 11
end
-- set @monthToShow= convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))-1
if(@monthToShow<0)
begin
set @monthToShow=0
end



declare @amonthbefore integer
set @amonthbefore=Month(@enddate)-1
if(@amonthbefore=0)
begin
set @amonthbefore=12
end


if(@amonthbefore in(1,3,5,7,8,10,12))
begin
set @dayToShow=31-DAY(@startdate)+DAY(@enddate)
end
if(@amonthbefore=2)
begin
IF (YEAR( @enddate ) % 4 = 0 AND YEAR( @enddate ) % 100 != 0) OR YEAR( @enddate ) % 400 = 0
begin
set @dayToShow=29-DAY(@startdate)+DAY(@enddate)
end
else
begin
set @dayToShow=28-DAY(@startdate)+DAY(@enddate)
end
end
if(@amonthbefore in (4,6,9,11))
begin
set @dayToShow=30-DAY(@startdate)+DAY(@enddate)
end




end
else
begin
--set @monthToShow=convert(int, DATEDIFF(mm,0,DATEADD(dd,DATEDIFF(dd,0,@enddate)- DATEDIFF(dd,0,@startdate),0)))-((convert(int,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25))*12))
if(month(@enddate)< month(@startdate))
begin
set @monthToShow=12+(month(@enddate)-month(@startdate))
end
else
begin
set @monthToShow= (month(@enddate)-month(@startdate))
end

set @dayToShow=DAY(@enddate)-DAY(@startdate)
end


SELECT
FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25) as [yearToShow],
@monthToShow as monthToShow ,@dayToShow as dayToShow ,
convert(varchar,FLOOR(DATEDIFF(day, @startdate, @enddate) / 365.25)) +' Year ' + convert(varchar,@monthToShow) +' months '+convert(varchar,@dayToShow)+' days ' as age

return
end

The following DIBOL program is a re hash of a program published in Computer World in the early 1970's.
It converts a date in the format yyyymmdd into the days that have elapsed since Jan 1 1900.
It also converts a number representing the number of days since Jan 1 1900 to a date.
All arithmetic is integer, so 8/3 = 2

Wow, very useful. Thank you very much for this. I'll use it often - thanks again.

select round((sysdate-to_date('17-jul-1988','dd-mm-yy'))/365) as years,
round(mod(months_between(sysdate,to_date('17-jul-1988','dd-mm-yy')),12)) as
months,
round(mod((sysdate-to_date('17-jul-1988','dd-mm-yy')),30)) as days from dual;

note:we can mention the date what ever we want or the columnname from the table instead of '17-jul-1988',bcz i give that date for your understand purpose.

you should mention which database system this works in

it will work in oracle database....

you should try getdate as the told you in above examples... or try datesub or datedd function... you know the rest best of luck
datesub is the main

we don't know if the input format is relevant to the question. is that the way the data is stored or input by the user.

the moral of the tale
1) store dates in a date column not a text / string / char / whatever column
once its in a date column then use the database date functions to manipulate the date value
2) provide more information about what db + front end if relevant you are using.

nice one