Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Age Calculation in SQL

    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

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    Which database are you using ?

  3. #3
    Join Date
    May 2002
    Posts
    13
    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.
    ==========================
    John Lambert
    Team Database
    The International Association Of Database Professionals
    www.TeamDatabase.com
    ==========================

  4. #4
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    For SQL SEVER GETDATE() returns the current date

    You can then use the DATEDIFF Function to calculate the difference

  5. #5
    Join Date
    Aug 2002
    Location
    Calcutta , INDIA
    Posts
    1
    Originally posted by rnealejr
    Which database are you using ?
    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>

  6. #6
    Join Date
    Aug 2007
    Posts
    1

    SQL 2005 Calculate Age Function Script

    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.

  7. #7
    Join Date
    Jul 2010
    Posts
    1

    Use this store procedure

    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

  8. #8
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,057
    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

    Code:
    #cat xdatex.dbl
    .SUBROUTINE XDATEX
    DATEXREF,    A
    RECORD DATEREC
               CLDRDATE, D8
                   CLDRYEAR, D4 @CLDRDATE 
                   CLDRMTH,  D2 @CLDRDATE +4    
                   CLDRDAY , D2 @CLDRDATE +6
               DAYS1901, D10            
               DAYOFWK,  D1
               NAMOFDAY, A9      
               NAMOFMTH, A9      
    RECORD 
            CALCYEAR , D10 
            CALCMTH ,D10
            CALCDAY , D10 
            DYYR , D10 
            DYWK , D10
            DYMO , D10 
            TEMPYEAR , D10 
            TEMPDAY , D10 
            LEAPYEAR , D10
            HOLDDATE , D8   
            M, A8
            DAYDATA, 7A9, 'SUNDAY   ','MONDAY   ','TUESDAY  ','WEDNESDAY'
    &,'THURSDAY ','FRIDAY   ','SATURDAY '
            MTHDATA, 12A9, 'JANUARY  ','FEBRUARY ','MARCH   ','APRIL   ',
    &'MAY     ','JUNE    ','JULY    ','AUGUST  ',
    &'SEPTEMBER','OCTOBER  ','NOVEMBER ','DECEMBER '
    PROC
    SOJ,
    DATEREC=DATEXREF
    HOLDDATE=CLDRDATE
    IF (DAYS1901 .NE. 0) GO TO CENTOCLD
    LEAPYEAR=2
    CALCYEAR= CLDRYEAR - 1900
    CALCMTH= CLDRMTH
    CALCDAY= CLDRDAY
    TEMPYEAR= CALCYEAR/4
    TEMPYEAR= TEMPYEAR * 4
    IF (TEMPYEAR .EQ. CALCYEAR)  LEAPYEAR=1
    DYYR= (CALCMTH * 275)/9 + CALCDAY - 30
    IF (CALCMTH .GT. 2) DYYR= DYYR -LEAPYEAR
    DAYS1901= CALCYEAR - 1
    DAYS1901= (DAYS1901 * 1461) / 4 + DYYR
    CALL    CENTOCLD
    IF (CLDRDATE .NE. HOLDDATE)
    BEGIN
    DAYS1901= 0
    CLDRDATE= 0
    DAYOFWK = 0
    NAMOFDAY=  
    NAMOFMTH=
    DATEXREF=DATEREC
    RETURN
    END
    RETURN
    CENTOCLD,
    CALCYEAR=(DAYS1901/1461) 
    CALCYEAR=(DAYS1901 - CALCYEAR + 364)/365
    DYYR=((CALCYEAR - 1)*1461)/4
    DYYR=DAYS1901-DYYR
    LEAPYEAR= 2
            TEMPYEAR= CALCYEAR/4
            TEMPYEAR= TEMPYEAR * 4
    IF (TEMPYEAR .EQ. CALCYEAR) LEAPYEAR=1
    TEMPDAY= DYYR
    TEMPYEAR= 61 - LEAPYEAR 
    IF (TEMPDAY .GT. TEMPYEAR) TEMPDAY= TEMPDAY + LEAPYEAR
    CALCMTH= (TEMPDAY * 9 + 269) / 275
    DYMO= ((CALCMTH * 275) / 9) - 30
    CALCDAY= TEMPDAY - DYMO
    DYMO=CALCDAY
    DYWK=DAYS1901 + 1
    DYWK=DYWK-((DYWK/7)*7)+1
    DAYOFWK=DYWK
    CLDRYEAR = CALCYEAR + 1900
    CLDRMTH= CALCMTH
    CLDRDAY= CALCDAY
    NAMOFDAY=  DAYDATA(DYWK)
    NAMOFMTH=MTHDATA(CALCMTH)
    DATEXREF=DATEREC
    RETURN
    #

  9. #9
    Join Date
    Sep 2010
    Location
    NY
    Posts
    3
    Wow, very useful. Thank you very much for this. I'll use it often - thanks again.

  10. #10
    Join Date
    Oct 2010
    Posts
    2

    how to find age in oracle

    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.
    Last edited by manojnaidu; 10-22-10 at 10:12.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by manojnaidu View Post
    select round((sysdate-to_date('17-jul-1988','dd-mm-yy'))/365) as years, ...
    you should mention which database system this works in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Oct 2010
    Posts
    2

    Thumbs up

    Quote Originally Posted by r937 View Post
    you should mention which database system this works in
    it will work in oracle database....

  13. #13
    Join Date
    Nov 2010
    Location
    karachi
    Posts
    1

    use getdate or datesub

    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

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Jul 2010
    Location
    ISHHHH
    Posts
    130
    Quote Originally Posted by Marcel Crudele View Post

    =======================================
    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)

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

    --Marcel Crudele
    --innerEcho, Inc.
    nice one
    WiTh Tnks & ReGaRdS
    mAtHuKuMaLi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •