October 29, 2012 at 2:30 pm
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
October 29, 2012 at 3:41 pm
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.
October 29, 2012 at 4:00 pm
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).
October 29, 2012 at 4:48 pm
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
Change is inevitable... Change for the better is not.
October 29, 2012 at 5:12 pm
/* 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) "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".
October 30, 2012 at 12:08 am
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
October 30, 2012 at 12:18 am
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
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply