Is there an accurate script for datediff format in years:months:days?

  • I've tryed several tsql scripts and none of them have proven to be accurate.

    If you got a neat script up your sleeve, i'd appreciate.

    Someting like this http://www.sqlservercentral.com/scripts/Age/75784/

    (which doesnt work for ('2011-01-16', '2013-01-01') )

    /*----------------------------------------------------------------------------------------------------------------------------

    Author :-ANAND BABU UCHAGAWKAR

    Purpose:-To find the datediff/age in text format (eg. 1 year(s), 10 month(s), 10 day(s)).

    DATE:-30-Aug-2011

    DATABASE:- SQL

    ----------------------------------------------------------------------------------------------------------------------------*/

    IF (Select COUNT(*) From Sysobjects Where [name] like 'FN_GETDATEDIFFTEXT') > 0

    BEGIN

    DROP FUNCTION FN_GETDATEDIFFTEXT

    END

    GO

    CREATE FUNCTION FN_GETDATEDIFFTEXT(@FromDate DateTime, @ToDate DateTime)

    RETURNS NVARCHAR(50)

    AS

    BEGIN

    Declare @daysDiff Int

    Declare @monthDiff Int

    Declare @yearDiff Int

    --Select @daysDiff = DATEDIFF(DAY, @FromDate, @ToDate)

    Set @monthDiff = ABS(DATEDIFF(MONTH, @FromDate, @ToDate)%12)

    Set @yearDiff = ABS(DATEDIFF(YYYY, @FromDate, @ToDate))

    -- If the From date month is greater than the month of the To date and the year difference is greater than zero

    -- then the year should the deducted by one

    IF DATEPART(MONTH,@FromDate) > DATEPART(MONTH,@ToDate) AND @yearDiff > 0

    BEGIN

    Set @yearDiff = @yearDiff - 1

    END

    IF DATEPART(DAY,@FromDate) > DATEPART(DAY, @ToDate)

    Begin

    --Get last date of the month of the FromDate

    Declare @lastDateOfMonth DateTime = DATEADD(MONTH, 1, @FromDate)

    Set @lastDateOfMonth = '01-' + DATENAME(MONTH,@lastDateOfMonth) + '-'+DATENAME(YEAR,@lastDateOfMonth)

    Set @lastDateOfMonth = DATEADD(DAY, -1, @lastDateOfMonth)

    Set @daysDiff = DATEDIFF(DAY, @FromDate, @lastDateOfMonth)

    Set @daysDiff = @daysDiff + DATEPART(DAY, @ToDate)

    Set @monthDiff = @monthDiff - 1

    End

    ELSE

    BEGIN

    Set @daysDiff = DATEPART(DAY, @ToDate) - DATEPART(DAY, @FromDate)

    END

    -- Select @yearDiff Yr, @monthDiff Mn, @daysDiff Dy

    RETURN

    CAST(@yearDiff as nvarchar) + ' year(s), ' +

    CAST(@monthDiff as nvarchar) + ' month(s), ' +

    CAST(@daysDiff as nvarchar) + ' day(s)'

    END

    GO

    Select DBO.FN_GETDATEDIFFTEXT('2011-01-16', '2013-01-01')

    -- Select DBO.FN_GETDATEDIFFTEXT('01-Jan-1990', Getdate())

  • Just threw this together from scratch. Test it out and see if you can break it.

    @date1 should always be prior to or the same as @date2

    declare @date1 date = '2012-01-15'

    declare @date2 date = '2013-01-16'

    declare @years int

    declare @months int

    declare @days int

    select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

    select @months = DATEdiff(month,@date1,@date2) % 12 - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

    select @months = case when @months >= 0 then @months else 12 + @months end

    select @days = DATEPART(dy,@date2) - DATEPART(dy,@date1)

    select @days = case when @days >=0 then @days else DATEDIFF(DAY,DATEADD(month,-1,@date2),@date2)+@days end

    select @years as [@years], @months as [@months], @days as [@days]

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • i have tehse saved in my snippets; does this do what you want?

    --elapsed time/datediff

    select

    [Days] = datediff(day,0,ET-ST)-1,

    [Hours] = datepart(Hour,ET-ST)-1,

    [Minutes] = datepart(Minute,ET-ST)-1,

    [Seconds] = datepart(Second,ET-ST),

    [Milliseconds] = datepart(millisecond,ET-ST)

    from

    (

    select -- Test Data

    ST = convert(datetime,'2008/09/22 00:35:33.997'),

    ET = convert(datetime,'2009/10/22 04:05:45.443')

    ) a

    select [Years] = datediff(year,0,ET-ST)-1,

    [Months] = datepart(month,ET-ST)-1,

    [Days] = datepart(day,ET-ST)-1,

    [Hours] = datepart(Hour,ET-ST),

    [Minutes] = datepart(Minute,ET-ST),

    [Seconds] = datepart(Second,ET-ST),

    [Milliseconds] = datepart(millisecond,ET-ST)

    from

    (

    select -- Test Data

    ST = convert(datetime,'2008/09/22 00:35:33.997'),

    ET = convert(datetime,'2009/10/23 04:05:45.443')

    ) a

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • select [Years] = datediff(year,0,ET-ST)-1,

    [Months] = datepart(month,ET-ST)-1,

    [Days] = datepart(day,ET-ST)-1

    from

    (

    select -- Test Data

    ST = convert(datetime,'2011/01/01'),

    ET = convert(datetime,'2013/01/13')

    ) a

    Gives 1 year, 0 months and 13 days.

  • declare @date1 date = '2010-05-17'

    declare @date2 date = '2013-01-16'

    declare @years int

    declare @months int

    declare @days int

    select @years = DATEDIFF(YEAR,@date1,@date2) - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

    select @months = DATEdiff(month,@date1,@date2) % 12 - case when datepart(day,@date1) > datepart(day,@date2) then 1 else 0 end

    select @months = case when @months >= 0 then @months else 12 + @months end

    select @days = DATEPART(dy,@date2) - DATEPART(dy,@date1)

    select @days = case when @days >=0 then @days else DATEDIFF(DAY,DATEADD(month,-1,@date2),@date2)+@days end

    select @years as [@years], @months as [@months], @days as [@days]

    Gives 2 years, 7 months, -90 days

  • Ummm.... how long is a month for this particular problem?

    --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 there's no way for dynamical months, then 30 days/month.

  • I think this will give you what you are looking for. It depends on the fact that the variables are set in left to right order. You can separate them out to single SET or SELECT statements if you're not comfortable with that.

    DECLARE @FromDate DateTime, @ToDate DateTime

    SELECT @FromDate = '2011-01-16', @ToDate = '2013-01-01'

    DECLARE @RefDate datetime

    ,@dayDiff Int

    ,@monthDiff Int

    ,@yearDiff Int

    IF @FromDate > @ToDate

    SELECT @RefDate = @FromDate, @FromDate = @ToDate, @ToDate = @RefDate

    SELECT @RefDate = DateAdd(Year, DateDiff(Year, @FromDate, @ToDate), @FromDate)

    ,@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Year, -1, @RefDate) ELSE @RefDate END

    ,@yearDiff = DateDiff(Year, @FromDate, @RefDate)

    ,@FromDate = @RefDate

    ,@RefDate = DateAdd(Month, DateDiff(Month, @FromDate, @ToDate), @FromDate)

    ,@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Month, -1, @RefDate) ELSE @RefDate END

    ,@monthDiff = DateDiff(Month, @FromDate, @RefDate)

    ,@FromDate = @RefDate

    ,@RefDate = DateAdd(Day, DateDiff(Day, @FromDate, @ToDate), @FromDate)

    ,@RefDate = CASE WHEN @RefDate > @ToDate THEN DateAdd(Day, -1, @RefDate) ELSE @RefDate END

    ,@dayDiff = DateDiff(Day, @FromDate, @RefDate)

    SELECT @yearDiff, @monthDiff, @dayDiff

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Lowell (1/17/2012)


    i have tehse saved in my snippets; does this do what you want?

    --elapsed time/datediff

    select [Years] = datediff(year,0,ET-ST)-1,

    [Months] = datepart(month,ET-ST)-1,

    [Days] = datepart(day,ET-ST)-1,

    [Hours] = datepart(Hour,ET-ST),

    [Minutes] = datepart(Minute,ET-ST),

    [Seconds] = datepart(Second,ET-ST),

    [Milliseconds] = datepart(millisecond,ET-ST)

    from

    (

    select -- Test Data

    ST = convert(datetime,'2008/09/22 00:35:33.997'),

    ET = convert(datetime,'2009/10/23 04:05:45.443')

    ) a

    This doesn't work as expected, because it doesn't appropriately account for leap years. For example, the range 2011-01-01 to 2012-12-31 contains a leap year (2012). Using ET-ST essentially converts the range to 1900-01-01 to 1902-01-01 which does NOT contain a leap year (1900 though divisible by 4 is not a leap year, because it is not divisible by 400) and subsequently gives the wrong values for year, month, and day.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • thanks drew;

    it's actually inaccurate in a lot of other situations too, depending on the range between the two dates; that date -1 stuff works just for the date supplied in the example; start putting in other dates and it blows up.

    your version is much more elegant, and I added it to my snippets.

    Thanks!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That's why I asked the question about how long a month was. It won't necessarily be accurate for 90 days with different start dates. Even 30 days is a poor estimate.

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

  • I think we use the day of the month as the "anniversary", and crossing an anniversary increments the year or the month. For example, if I was married on Jan 10th, every year on Jan 10th I increment the number of years I have been married, regardless of whether the time between the dates had 365 or 366 days. Similarly, when something happens on the 10th day of a month, the next 10th day is the one-month anniversay. If I started dating my wife on Jan 10th, Feb 10th is our one-month anniversary and Mar 10th is our two-month anniversary, despite the fact that the number of days between those intervals does not match.

    I suggest that when the day and month of the end date equals or passes the day and month of the start year, then the year increments, and when the day of the end month equals or passes the day of the start month then the month increments.

    Therefore, the difference between Feb 28 of one year and Mar 29 of the following year is always 1 year, 1 month, 1 day, whether or not the time included a leap day. Feb 28 to Feb 28 increments the year. 28 to 28 increments the month.

  • Fahey's logic above is what I used in coding my response, since that is how ages are typically calculated and age was specifically mentioned.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Age Function F_AGE_YYYY_MM_DD

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729

    This function calculates age in years, months and days from @START_DATE through @END_DATE and returns the age in format YYYY MM DD.

    Years is the number of full years between @START_DATE and @END_DATE.

    Months is the number of full months since the last full year anniversary.

    Days is the number of days since the last full month anniversary.

  • Try This.

    DECLARE @date1 DATETIME,

    @date2 DATETIME,

    @year INT,

    @month INT,

    @days INT,

    @hours INT,

    @min-2 INT,

    @sec INT

    SELECT @date1 = '2012-10-29 18:34:31.013',

    @date2 = '2013-12-29 20:54:41.056'

    select @year = DATEDIFF(yy,@date1, @date2),

    @date1 = DATEADD(yy,@year,@date1),

    @month = DATEDIFF(MM,@date1, @date2),

    @date1 = DATEADD(MM,@month,@date1),

    @days = DATEDIFF(DD,@date1, @date2),

    @date1 = DATEADD(dd,@days,@date1),

    @hours = DATEDIFF(hh,@date1, @date2),

    @date1 = DATEADD(hh,@hours,@date1),

    @min-2 = DATEDIFF(MI,@date1, @date2),

    @date1 = DATEADD(mi,@min,@date1),

    @sec = DATEDIFF(SS,@date1, @date2)

    SELECT CASE WHEN @year = 0 THEN '' ELSE CAST (@year AS VARCHAR)+'Y ' END+

    CASE WHEN @month = 0 THEN '' ELSE CAST (@month AS VARCHAR)+'M ' END+

    CASE WHEN @days = 0 THEN '' ELSE CAST (@days AS VARCHAR)+'D ' END+

    CASE WHEN @hours = 0 THEN '' ELSE CAST (@hours AS VARCHAR)+'H ' END+

    CASE WHEN @min-2 = 0 THEN '' ELSE CAST (@min AS VARCHAR)+'Mi ' END+

    CASE WHEN @sec = 0 THEN '' ELSE CAST (@sec AS VARCHAR)+'Sec ' END

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

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