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

Question

Post reply

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

Viewing 7 posts - 16 through 21 (of 21 total)

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 1

I think this should do it.

declare @FromDate DateTime, @ToDate DateTime

declare @TempFromDate datetime

Set @FromDate ='2011-01-16'

Set @ToDate = '2013-01-01'

Declare @daysDiff Int

Declare @monthDiff Int

Declare @yearDiff Int

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

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

Select @TempFromDate = DATEADD(yyyy,@yearDiff,@FromDate)

Select @FromDate as FromDate, @ToDate as ToDate

If @TempFromDate > @ToDate

Begin

Set @yearDiff = @yearDiff -1

End

Select @TempFromDate = DATEADD(yyyy,@yearDiff,@FromDate)

--Select @TempFromDate as [@TempFromDateAfterYear]

Set @monthDiff = DATEDIFF(MONTH, @TempFromDate, @ToDate)

--Select @monthDiff as [@monthDiff]

Select @TempFromDate = DATEADD(mm,@monthDiff,@TempFromDate)

If @TempFromDate > @ToDate

Begin

Set @monthDiff = @monthDiff -1

Select @TempFromDate = DATEADD(mm,-1,@TempFromDate)

End

--Select @TempFromDate as [@TempFromDateAfterYearAfterMonth]

Set @daysDiff = DATEDIFF(dd, @TempFromDate, @ToDate)

Select @yearDiff as YearDiff,@monthDiff as MonthDiff,@daysDiff as DayDiff

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 2

Since we are throwing hats in rings, here is what I would use:

DECLARE@StartDateDATETIME = '19800510',

@EndDateDATETIME = GETDATE()

;WITHYearDiff

AS(SELECTDATEDIFF(YEAR, @StartDate, @EndDate) AS YearCount),

MonthDiff

AS(

SELECTYearCount,

DATEADD(YEAR, YearCount, @StartDate) AS YearBase,

DATEDIFF(MONTH, DATEADD(YEAR, YearCount, @StartDate), @EndDate) AS MonthCount

FROMYearDiff

)

SELECTCAST(YearCount AS VARCHAR(4)) + ':' +

CAST(MonthCount AS VARCHAR(2)) + ':' +

CAST(DATEDIFF(DAY, DATEADD(MONTH, MonthCount, YearBase), @EndDate) AS VARCHAR(2))

FROMMonthDiff

Perhaps someone can find a case where this doesn't work properly so I can fix it.

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 3

Here is the code in Function format:

SETANSI_NULLS ON

SETANSI_PADDING ON

IFOBJECTPROPERTY(OBJECT_ID('YMDDiff'), 'IsInlineFunction') IS NOT NULL

DROP

FUNCTIONYMDDiff

GO

CREATE

FUNCTION dbo.YMDDiff

(--<parameters>

@StartDateDATETIME,--<param description="The starting date to use to calculate YMDDiff." />

@EndDateDATETIME--<param description="The ending date to use to calculate YMDDiff." />

)--</parameters>

RETURNSVARCHAR(10)

AS

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

/*<summary>

Calculates the number of years, months, and days between two given dates.

</summary>*/

-- History:Author:Revision:

--2012.10.29Jonathan FaheyCreation

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

-- SELECT dbo.YMDDiff('2000-01-01', '2004-03-10') 'YMDDiff'

BEGIN

DECLARE@ReturnStringVARCHAR(10)

;WITHYearDiff

AS(SELECTDATEDIFF(YEAR, @StartDate, @EndDate) AS YearCount),

MonthDiff

AS(

SELECTYearCount,

DATEADD(YEAR, YearCount, @StartDate) AS YearBase,

DATEDIFF(MONTH, DATEADD(YEAR, YearCount, @StartDate), @EndDate) AS MonthCount

FROMYearDiff

)

SELECT@ReturnString =

(

--CAST(YearCount AS VARCHAR(4)) + ':' +

--CAST(MonthCount AS VARCHAR(2)) + ':' +

--CAST(DATEDIFF(DAY, DATEADD(MONTH, MonthCount, YearBase), @EndDate) AS VARCHAR(2))

RIGHT('0000' + CAST(YearCount AS VARCHAR(4)), 4) + ':' +

RIGHT('00' + CAST(MonthCount AS VARCHAR(2)), 2) + ':' +

RIGHT('00' + CAST(DATEDIFF(DAY, DATEADD(MONTH, MonthCount, YearBase), @EndDate) AS VARCHAR(2)), 2)

)

FROMMonthDiff

RETURN@ReturnString

END

GO

GRANT EXECUTE ON YMDDiff TO [XXXXXX]

GO

I couldn't figure out how to use a single "RETURN" statement, so I used a variable instead. It has something to do with the "WITH" statement. The current format returns zero-padded numbers (0004:02:09); the commented version returns just the raw numbers (4:2:9).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

fahey.jonathan (10/29/2012)
Perhaps someone can find a case where this doesn't work properly so I can fix it.

How about the following "errors"???

DECLARE@StartDateDATETIME

SELECT @StartDate = '20120228'

DECLARE @EndDateDATETIME

SELECT @EndDate = '20120301'

;WITHYearDiff

AS(SELECTDATEDIFF(YEAR, @StartDate, @EndDate) AS YearCount),

MonthDiff

AS(

SELECTYearCount,

DATEADD(YEAR, YearCount, @StartDate) AS YearBase,

DATEDIFF(MONTH, DATEADD(YEAR, YearCount, @StartDate), @EndDate) AS MonthCount

FROMYearDiff

)

SELECTCAST(YearCount AS VARCHAR(4)) + ':' +

CAST(MonthCount AS VARCHAR(2)) + ':' +

CAST(DATEDIFF(DAY, DATEADD(MONTH, MonthCount, YearBase), @EndDate) AS VARCHAR(2))

FROMMonthDiff

Results:

0:1:*

Other than the obvious "*" error, it's really hard for me to believe that anyone would consider a 2 or 3 day span (depending on if the end date was temporally a closed or open data) to be a "month". And that's the problem with these requests. What is a "month"? DATEDIFF can report a change in month within a date range that spans mere milliseconds.

How about for years?

DECLARE@StartDateDATETIME

SELECT @StartDate = '20121231'

DECLARE @EndDateDATETIME

SELECT @EndDate = '20130101'

;WITHYearDiff

AS(SELECTDATEDIFF(YEAR, @StartDate, @EndDate) AS YearCount),

MonthDiff

AS(

SELECTYearCount,

DATEADD(YEAR, YearCount, @StartDate) AS YearBase,

DATEDIFF(MONTH, DATEADD(YEAR, YearCount, @StartDate), @EndDate) AS MonthCount

FROMYearDiff

)

SELECTCAST(YearCount AS VARCHAR(4)) + ':' +

CAST(MonthCount AS VARCHAR(2)) + ':' +

CAST(DATEDIFF(DAY, DATEADD(MONTH, MonthCount, YearBase), @EndDate) AS VARCHAR(2))

FROMMonthDiff

;

Results:

1:*:*

Once again, we have the obvious errors and the not so obvious. Should a 1 day span really be reported as having a year gone by? I don't believe that's the intent here.

--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)
Intro to Tally Tables and Functions

ScottPletcher SSC Guru Points: 100262 More actions · Answer 5

/* Author: ScottPletcher */

SELECT

start_date,

end_date AS end_date,

CAST(

DATEDIFF(YEAR, start_date, end_date) - CASE WHEN SUBSTRING(start_date, 5, 4) > SUBSTRING(end_date, 5, 4) THEN 1 ELSE 0 END

AS varchar(4)) + ' years ' +

CAST(

CASE WHEN SUBSTRING(start_date, 5, 4) > SUBSTRING(end_date, 5, 4)

THEN 12 + CAST(SUBSTRING(end_date, 5, 2) AS int) - CAST(SUBSTRING(start_date, 5, 2) AS int)

ELSE DATEDIFF(MONTH, start_date, end_date) % 12 END

- CASE WHEN RIGHT(start_date, 2) > RIGHT(end_date, 2) THEN 1 ELSE 0 END

AS varchar(2)) + ' months ' +

CAST(

CASE WHEN RIGHT(start_date, 2) > RIGHT(end_date, 2)

THEN DATEDIFF(DAY, STUFF(CONVERT(char(8), DATEADD(MONTH, -1, end_date), 112), 7, 2, SUBSTRING(start_date, 7, 2)), end_date)

ELSE CAST(RIGHT(end_date, 2) AS int) - CAST(RIGHT(start_date, 2) AS int)

END

AS varchar(2)) + ' days '

--just sample test data, replace with your table / other test data

FROM (

SELECT '20050704' AS start_date, '20120921' AS end_date UNION ALL

SELECT '20050922', '20120921' UNION ALL

SELECT '20051008', '20120921' UNION ALL

SELECT '20051022', '20120921' UNION ALL

SELECT '20070228', '20080301' UNION ALL

SELECT '20120228', '20120301' /* row added via Edit */

) AS start_dates

Edit: Added one more sample row of data, based on someone else's post of potential date computation error.

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!

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 6

Are we looking for something like this?

/****** Object: UserDefinedFunction [dbo].[fn_datediff] Script Date: 10/30/2012 00:05:42 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_datediff]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_datediff]

GO

create function dbo.fn_datediff(@FirstDate date, @SecondDate date)

returns table

as return(

with FixDates as (

select

case when @FirstDate < @SecondDate then @FirstDate else @SecondDate end as FromDate,

case when @FirstDate < @SecondDate then @SecondDate else @FirstDate end as ToDate

),

initialdiff as (

select

datediff(mm,FromDate,ToDate) as InitDiff

from

FixDates

),

correcteddiff as (

select

InitDiff - case when dateadd(mm,InitDiff, FromDate) > ToDate then 1 else 0 end as CorrDiff

from

initialdiff

cross join FixDates

)

select

cast(CorrDiff/12 as varchar(5)) + ' year(s), ' +

cast(CorrDIff % 12 as varchar(2)) + ' month(s), ' +

cast(datediff(dd, dateadd(mm, CorrDiff, FromDate), ToDate) as varchar(2)) + ' day(s)' as DateDifference

from

correcteddiff

cross join FixDates);

GO

declare @FirstDate date = '20110116',

@SecondDate date = '20130101';

select * from dbo.fn_datediff(@FirstDate, @SecondDate);

GO

declare @FirstDate date = '20121231',

@SecondDate date = '20130101';

select * from dbo.fn_datediff(@FirstDate, @SecondDate);

GO

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

Added Scott's sample data to the mix:

/****** Object: UserDefinedFunction [dbo].[fn_datediff] Script Date: 10/30/2012 00:05:42 ******/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_datediff]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

DROP FUNCTION [dbo].[fn_datediff]

GO

create function dbo.fn_datediff(@FirstDate date, @SecondDate date)

returns table

as return(

with FixDates as (

select

case when @FirstDate < @SecondDate then @FirstDate else @SecondDate end as FromDate,

case when @FirstDate < @SecondDate then @SecondDate else @FirstDate end as ToDate

),

initialdiff as (

select

datediff(mm,FromDate,ToDate) as InitDiff

from

FixDates

),

correcteddiff as (

select

InitDiff - case when dateadd(mm,InitDiff, FromDate) > ToDate then 1 else 0 end as CorrDiff

from

initialdiff

cross join FixDates

)

select

cast(CorrDiff/12 as varchar(5)) + ' year(s), ' +

cast(CorrDIff % 12 as varchar(2)) + ' month(s), ' +

cast(datediff(dd, dateadd(mm, CorrDiff, FromDate), ToDate) as varchar(2)) + ' day(s)' as DateDifference

from

correcteddiff

cross join FixDates);

GO

declare @FirstDate date = '20110116',

@SecondDate date = '20130101';

select * from dbo.fn_datediff(@FirstDate, @SecondDate);

GO

declare @FirstDate date = '20121231',

@SecondDate date = '20130101';

select * from dbo.fn_datediff(@FirstDate, @SecondDate);

GO

select

*

from

(

SELECT cast('20050704' as date) AS sdate, cast('20120921' as date) AS edate UNION ALL

SELECT cast('20050922' as date), cast('20120921' as date) UNION ALL

SELECT cast('20051008' as date), cast('20120921' as date) UNION ALL

SELECT cast('20051022' as date), cast('20120921' as date) UNION ALL

SELECT cast('20070228' as date), cast('20080301' as date) UNION ALL

SELECT cast('20120228' as date), cast('20120301' as date) /* row added via Edit */

) AS start_dates

cross apply dbo.fn_datediff(sdate, edate);

GO