Post reply

Date Difference

  • November 17, 2015 at 4:59 am

    #306502

    i want to calculate the age in the form of years & months& days.

    The difference between two dates Date_of_Birth and Today date in the form of

    years months and days...

    Thanks

  • November 17, 2015 at 5:41 am

    #1840287

    Others may come up with other solutions

    DECLARE @dayOfBirth datetime = '19850723'

    DECLARE @today datetime, @thisYearBirthDay datetime, @years int, @months int, @days int

    SELECT @today = GETDATE()

    SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dayOfBirth, @today), @dayOfBirth)

    SELECT @years = DATEDIFF(year, @dayOfBirth, @today) - (CASE WHEN @thisYearBirthDay > @today THEN 1 ELSE 0 END)

    SELECT @months = MONTH(@today - @thisYearBirthDay) - 1

    SELECT @days = DAY(@today - @thisYearBirthDay) - 1

    SELECT @years, @months, @days

  • November 17, 2015 at 9:33 am

    #1840392

    First you'll need to decide how you want to handle birthdays on Feb 29. In non-leap years, do you want to treat Feb 28 as the birthday, or March 1? Note: I believe that in some jurisdictions there are actually legal requirements around this for certain date calculations.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

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