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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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

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