Calculating age

  • hi friends

    i have Date of Birth column in my database , i dont have age column

    i want to calculate accurate Age form DOB..

    please help me..

  • DECLARE @DOB datetime

    SET @DOB = '1/20/1980'

    SELECT CASE

    WHEN DATEPART(DY,GETDATE()) >= DATEPART(DY,@DOB)

    THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Simple way to compute your age accurately.

    A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).

    In this query you exactly get the age as is it.

    Example 1

    DOB = 11/15/1987 and

    datenow =11/15/2012 the result would be

    AGE=25

    Example 2

    DOB = 11/14/1987 and

    datenow =11/15/2012 the result would be

    AGE=24

    SO HERE ARE THE QUERY

    DECLARE @DOB SMALLDATETIME

    SELECT @DOB = '11/15/1987'

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

    HOPE I CAN HELP! 🙂

  • martinez.math (11/16/2012)


    Simple way to compute your age accurately.

    A lot of query not exactly compute the age? yes! because sometime they only compute the datediff between DOB and datenow and divide it to 365.25 days and as a result they get a number with decimal something like 25 is the age with .06 decimal (age=25.06).

    In this query you exactly get the age as is it.

    Example 1

    DOB = 11/15/1987 and

    datenow =11/15/2012 the result would be

    AGE=25

    Example 2

    DOB = 11/14/1987 and

    datenow =11/15/2012 the result would be

    AGE=24

    SO HERE ARE THE QUERY

    DECLARE @DOB SMALLDATETIME

    SELECT @DOB = '11/15/1987'

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

    HOPE I CAN HELP! 🙂

    You just responded to a three-year-old post!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/16/2012)


    You just responded to a three-year-old post!

    So, just add 3 to the result and all will be well 😉

  • It is very simple, just use the following sql statement,

    (DATEDIFF(YY,DOB,GETDATE()) - CASE

    WHEN MONTH(DOB)<MONTH(GETDATE()) AND (MONTH(DOB)=MONTH(GETDATE()) OR DAY(DOB)>DAY(GETDATE())) THEN 0 ELSE 1

    END)

    Here DOB is Columname, and GETDATE() is a function which gives the current date.

  • Garadin (1/10/2009)


    DECLARE @DOB datetime

    SET @DOB = '1/20/1980'

    SELECT CASE

    WHEN DATEPART(DY,GETDATE()) >= DATEPART(DY,@DOB)

    THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END

    Oh, be careful now. I know this is an old post but the code above doesn't work 100% of the time. For example, it returns "0" years if @DOB = '2000-03-31' and the current date is '2001-03-31'. The problem is that DY contains different values for dates after 28 Feb for ALL leap years.

    Try it yourself...

    DECLARE @DOB datetime

    ,@Now datetime

    SELECT @DOB = '3/31/2000'

    ,@Now = '3/31/2001'

    SELECT CASE

    WHEN DATEPART(DY,@Now) >= DATEPART(DY,@DOB)

    THEN DATEDIFF(YY,@DOB,@Now)

    ELSE DATEDIFF(YY,@DOB,@Now)-1

    END

    ;

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • If you believe that people that were born on the last day of February on a leap year turn a year older on the last day of February on non-leap years, this appears to work although I'll admit that I've not tested very many possibilities.

    DECLARE @DOB DATETIME

    ,@Now DATETIME

    SELECT @DOB = '2000-02-29'

    ,@Now = '2001-02-28'

    SELECT DATEDIFF(yy,@DOB,@Now)

    - CASE

    WHEN @Now < DATEADD(yy,DATEDIFF(yy,@DOB,@Now),@DOB)

    THEN 1

    ELSE 0

    END

    ;

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • math martinez (11/16/2012)


    SO HERE ARE THE QUERY

    DECLARE @DOB SMALLDATETIME

    SELECT @DOB = '11/15/1987'

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(GETDATE()) AND DAY(@DOB) >=DAY(GETDATE()) THEN DATEDIFF(YY,@DOB,GETDATE())

    ELSE DATEDIFF(YY,@DOB,GETDATE())-1

    END AS AGE

    HOPE I CAN HELP! 🙂

    It also has a Leap Year bug. I changed GETDATE() in your code to @Now to make it simple to test. Notice that your code says a person is 1 year old for the given dates, which is incorrect..

    DECLARE @DOB DATETIME

    ,@Now DATETIME

    SELECT @DOB = '2000-03-31'

    ,@Now = '2001-03-30'

    SELECT

    CASE

    WHEN MONTH(@DOB) >= MONTH(@Now) AND DAY(@DOB) >=DAY(@Now) THEN DATEDIFF(YY,@DOB,@Now)

    ELSE DATEDIFF(YY,@DOB,@Now)-1

    END AS AGE

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • hariharannkl (9/7/2013)


    It is very simple, just use the following sql statement,

    (DATEDIFF(YY,DOB,GETDATE()) - CASE

    WHEN MONTH(DOB)<MONTH(GETDATE()) AND (MONTH(DOB)=MONTH(GETDATE()) OR DAY(DOB)>DAY(GETDATE())) THEN 0 ELSE 1

    END)

    Here DOB is Columname, and GETDATE() is a function which gives the current date.

    It would appear that your's also has a Leap Year problem but in the opposite direction. Your's returns "0" for the following dates which is incorrect.

    DECLARE @DOB DATETIME

    ,@Now DATETIME

    SELECT @DOB = '2000-03-31'

    ,@Now = '2001-03-31'

    SELECT DATEDIFF(YY,@DOB,@Now) - CASE

    WHEN MONTH(@DOB)<MONTH(@Now) AND (MONTH(@DOB)=MONTH(@Now) OR DAY(@DOB)>DAY(@Now)) THEN 0 ELSE 1

    END

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This seems to work

    DECLARE @DOB DATETIME

    SET @DOB='9/19/2000'

    SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)

  • gilbert delarosa (9/20/2013)


    This seems to work

    DECLARE @DOB DATETIME

    SET @DOB='9/19/2000'

    SELECT FLOOR(DATEDIFF(DD,@DOB,GETDATE())/365.25)

    Good try but it doesn't work...

    DECLARE @DOB DATETIME

    SET @DOB='01/01/2013'

    SELECT FLOOR(DATEDIFF(DD,@DOB,'01/01/2014')/365.25)

    Results:

    ---------------------------------------

    0

    (1 row(s) affected)

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • But they wouldn't be 1 until after their BDay

    or you could just

    DECLARE @DOB DATETIME

    SET @DOB='01/01/2013'

    SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)

  • gilbert delarosa (9/20/2013)


    But they wouldn't be 1 until after their BDay

    or you could just

    DECLARE @DOB DATETIME

    SET @DOB='01/01/2013'

    SELECT FLOOR((DATEDIFF(DD,@DOB,'01/01/2014')+1)/365.25)

    Still doesn't work. Last I heard, you gained a year on your birthday and not the day before (Feb 29 babies sometimes excluded). 🙂

    DECLARE @DOB DATETIME

    SET @DOB='01/01/2012'

    SELECT FLOOR((DATEDIFF(DD,@DOB,'12/31/2012')+1)/365.25)

    Results:

    ---------------------------------------

    1

    (1 row(s) affected)

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • declare @dob datetime,@now date

    set @dob='1 jan 2012'

    set @now = '31 dec 2012'

    select

    datepart(year,@now) - datepart(year,@dob)

    +

    case

    when dateadd(day

    ,datepart(day,@dob)-1

    ,dateadd(month

    ,datepart(month,@dob)-1

    ,dateadd(year

    ,datepart(year,@now)-1800

    ,'1 jan 1800'

    )

    )

    ) > @now

    then -1

    else 0

    end

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply