How to use DATEDIFF to return year, month and day?

问题:

How can I use DATEDIFF to return the difference between two dates in years, months and days in SQL Server 2005

DATEDIFF (date , date)

How to result that: 2 year 3 month 10 day

Can anyone complete this t-sql?

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @getmm INT
    DECLARE @dd INT

    SET @yy = DATEDIFF(yy, @dstart, @dend)
    SET @mm = DATEDIFF(mm, @dstart, @dend)
    SET @dd = DATEDIFF(dd, @dstart, @dend)
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))

    RETURN (
        Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@mm) + 'month'  + Convert(varchar(10),@dd) + 'day'
        )
END

回答1:

Here's my solution to Eric's function:

DECLARE @getmm INT
DECLARE @getdd INT

SET @yy = DATEDIFF(yy, @dstart, @dend)
SET @mm = DATEDIFF(mm, @dstart, @dend)
SET @dd = DATEDIFF(dd, @dstart, @dend)
SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))
SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))

RETURN (
  Convert(varchar(10),@yy) + 'year' + Convert(varchar(10),@getmm) + 'month'  + Convert(varchar(10),@getdd) + 'day'
)

Good call on the use of ABS to handle if the start date is after the end date.

---

This:

WITH ex_table AS (
  SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime')
SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM ex_table t

..or non-CTE using for SQL Server 2000 and prior:

SELECT CAST(DATEDIFF(yy, t.birthdatetime, t.visitdatetime) AS varchar(4)) +' year '+
       CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime) AS varchar(2)) +' month '+
       CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime), t.visitdatetime), DATEADD(yy, DATEDIFF(yy, t.birthdatetime, t.visitdatetime), t.birthdatetime)), t.visitdatetime) AS varchar(2)) +' day' AS result
  FROM (SELECT '2007-01-01' 'birthdatetime',
         '2009-03-29' 'visitdatetime') t

...will return:

result
----------------------
2 year 2 month 28 day

Reference: DATEDIFF

回答2:

It works for some of the situations but when you are subtracting dates like 2007-10-15 (DateHired) from 2011-01-13 (dateterminated) it gives you a negative number when the abs isn't around it but putting the abs around doesn't fix it either because then the year and month figure are incorrect.

回答3:

Create this function, it will give exact date difference like year months days

    Create function get_Exact_Date_diff(@date smalldatetime,@date2 smalldatetime)
 returns varchar(50)

    as

    begin

    declare @date3 smalldatetime

    Declare @month int,@year int,@day int

     if @date>@date2
     begin
     set @date3=@date2
     set @date2=@date
     set @date=@date3
     end



    SELECT @month=datediff (MONTH,@date,@date2)

    if dateadd(month,@month,@date) >@date2
    begin
    set @month=@month-1
    end
    set @day=DATEDIFF(day,dateadd(month,@month,@date),@date2)

    set @year=@month/12
    set @month=@month % 12

    return (case when @year=0 then '' when @year=1 then convert(varchar(50),@year ) + ' year ' when @year>1 then convert(varchar(50),@year ) + ' years ' end)
    + (case when @month=0 then '' when @month=1 then convert(varchar(50),@month ) + ' month ' when @month>1 then convert(varchar(50),@month ) + ' months ' end)
    + (case when @day=0 then '' when @day=1 then convert(varchar(50),@day ) + ' day ' when @day>1 then convert(varchar(50),@day ) + ' days ' end)

    end

回答4:

I know there are a few answers already here, but I thought I'd add what I came up with as (at least to me) it seems very simple to follow:

CREATE FUNCTION dbo.fn_DateDiff_YMDMHS
(   
    @Startdate as datetime2(0),
    @Enddate as datetime2(0)
)
RETURNS TABLE 
AS
RETURN 
(
    select 
        TotalYears [Years],
        datediff(month, dateadd(Year, TotalYears, @Startdate), @Enddate) Months,
        datediff(day, dateadd(month, TotalMonths, @Startdate), @Enddate) [Days],
        datediff(hour, dateadd(day, TotalDays, @Startdate), @Enddate) [Hours],
        datediff(minute, dateadd(hour, TotalHours, @Startdate), @Enddate) [Minutes],
        datediff(second, dateadd(minute, TotalMinutes, @Startdate), @Enddate) [Seconds]
    from (
    select 
        datediff(SECOND, @Startdate, @Enddate) TotalSeconds,
        datediff(minute, @Startdate, @Enddate) TotalMinutes,
        datediff(hour, @Startdate, @Enddate) TotalHours,
        datediff(day, @Startdate, @Enddate) TotalDays,
        datediff(month, @Startdate, @Enddate) TotalMonths,
        datediff(year, @Startdate, @Enddate) TotalYears) DateDiffs
    )

Then when you call with:

select * from dbo.fn_DateDiff_YMDMHS('1900-01-01 00:00:00', '1910-10-05 03:01:02')

You'll get this returned:

Years   Months  Days    Hours   Minutes Seconds
10      9       4       3       1       2

Obviously you could change this to a formatted output and have a scalar variable instead, but I'll leave that to you :-)

EDIT:

I've ended up needing to also do a time ago function to return a format like "5 years and 2 days ago"

CREATE FUNCTION fn_DateDiff_YMDMHS_String
(
    @StartDate datetime2(0),
    @EndDate datetime2(0),
    @OutputYears bit = 1,
    @OutputMonths bit = 1,
    @OutputDays bit = 1,
    @OutputHours bit = 0,
    @OutputMinutes bit = 0,
    @OutputSeconds bit = 0,
    @OutputSuffix bit = 0
)
RETURNS varchar(256)
AS
BEGIN
    DECLARE @Output varchar(256) = ''
    declare @Years int, @Months int, @Days int, @Hours int, @Minutes int, @Seconds int

    select 
        @Years = case when @OutputYears = 1 then Years else 0 end,
        @Months = case when @OutputMonths = 1 then Months else 0 end,
        @Days = case when @OutputDays = 1 then Days else 0 end,
        @Hours = case when @OutputHours = 1 then Hours else 0 end,
        @Minutes = case when @OutputMinutes = 1 then Minutes else 0 end,
        @Seconds = case when @OutputSeconds = 1 then Seconds else 0 end 
    from dbo.fn_DateDiff_YMDMHS(@StartDate, @EndDate)

    declare @and varchar(5) = ''
    if @OutputYears = 1 and @Years > 0 
    begin
        set @Output = @Output + cast(@Years as varchar(4)) + ' year' 
        if @Years > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Months > 0 and @Days + @Hours + @Minutes + @Seconds = 0 set @and = 'and '
    end
    if @OutputMonths = 1 and @Months > 0 
    begin
        set @Output = @Output + @and + cast(@Months as varchar(2)) + ' month'
        if @Months > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Days > 0 and @Hours + @Minutes + @Seconds = 0 set @and = 'and '
    end
    if @OutputDays = 1 and @Days > 0 
    begin
        set @Output = @Output + @and + cast(@Days as varchar(2)) + ' day'
        if @Days > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Hours > 0 and @Minutes + @Seconds = 0 set @and = 'and '
    end
    if @OutputHours = 1 and @Hours > 0 
    begin
        set @Output = @Output + @and + cast(@Hours as varchar(2)) + ' hour'
        if @Hours > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Minutes > 0 and @Seconds = 0 set @and = 'and '
    end
    if @OutputMinutes = 1 and @Minutes > 0 
    begin
        set @Output = @Output + @and + cast(@Minutes as varchar(2)) + ' minute'
        if @Minutes > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
        if @Seconds > 0 set @and = 'and '
    end
    if @OutputSeconds = 1 and @Seconds > 0 
    begin
        set @Output = @Output + @and + cast(@Seconds as varchar(2)) + ' second'
        if @Seconds > 1 set @Output = @Output + 's ' else set @Output = @Output + ' '
    end

    if @OutputSuffix = 1
    begin
        if @StartDate < @EndDate
        begin
            set @Output = @Output + 'ago'
        end
        else
        begin
            set @Output = 'in ' + @Output
        end
    end

    RETURN @Output

END

Here are 2 examples

select dbo.fn_DateDiff_YMDMHS_String('2000-01-01 00:00:00', '2007-09-19 14:39:53', 1, 1, 1, 1,1,1, 1)
7 years 8 months 18 days 8 hours 39 minutes and 53 seconds ago

and

select dbo.fn_DateDiff_YMDMHS_String('2000-01-01 00:00:00', '2007-09-19 14:39:53', 1, 1, 1, 0,0,0, 1)
7 years 8 months and 18 days ago

I hope this is of use to someone in the future anyway as I couldn't find much on anything like this when searching (could just be one of those days though) I'm open to improvements as I know I'm not always the most compact or fastest code programmer :-)

Regards

Liam

回答5:

Check this page... http://www.sqlteam.com/article/datediff-function-demystified

Create this functions:

CREATE FUNCTION dbo.fnYearsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(month, @FromDate, @ToDate)
               END / 12
END

and

CREATE FUNCTION dbo.fnMonthsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(month, @FromDate, @ToDate)
               END
END

and finally

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @dd INT
    DECLARE @getmm INT
    DECLARE @getdd INT

    SET @yy = dbo.fnYearsApart(@dstart, @dend)  --DATEDIFF(yy, @dstart, @dend)
    SET @mm = dbo.fnMonthsApart(@dstart, @dend) --DATEDIFF(mm, @dstart, @dend)
    SET @dd = DATEDIFF(dd, @dstart, @dend)
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend))
    SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))

    RETURN (
      Convert(varchar(10),@yy) + ' años, ' + Convert(varchar(10),@getmm) + ' meses, '  + Convert(varchar(10),@getdd) + ' días'
    )
END

Greats!

回答6:

The Modified Function

ALTER FUNCTION [dbo].[gatYMD](@dstart VARCHAR(50), @dend VARCHAR(50))
RETURNS VARCHAR(50) AS
BEGIN
    DECLARE @yy INT
    DECLARE @mm INT
    DECLARE @getdd INT
    DECLARE @dd INT

    SET @yy = DATEDIFF(yy, @dstart, @dend)
    SET @mm = DATEDIFF(mm, @dstart, @dend) - (12 * @yy)
    SET @dd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @dstart), @dend), DATEADD(yy, @yy, @dstart)), @dend))


    Return (Convert(varchar(10),@yy) + ' year ' + Convert(varchar(10),@mm) + ' month '  + Convert(varchar(10),@dd) + ' day ')

END

回答7:

Using ParseName

DECLARE
  @ReportBeginDate DATE

SET @ReportBeginDate='2015-01-01';

IF OBJECT_ID('TEMPDB..#tmp_ymd') IS NOT NULL
BEGIN
DROP TABLE #tmp_ymd;
END;

select
cast(cast(datediff(mm,@ReportBeginDate,getdate()) as decimal (10,2))/12 as decimal(10,2)) as YearMonthDec
,cast(datediff(dd,@ReportBeginDate,getdate()) as decimal (10,2)) as DayDec
into #tmp_ymd

select
YearMonthDec
,cast(parsename(YearMonthDec,2) as decimal (10,0)) as yearnum
,cast(cast(parsename(YearMonthDec,1) as decimal (10,0))/100*(12) as numeric) as monthnum
,case when YearMonthDec>=1 then datediff(dd,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101),getdate()) else DayDec end as daynum

from #tmp_ymd

回答8:

CREATE FUNCTION FindDateDiff(@Date1 date,@Date2 date)
RETURNS TABLE 
AS
RETURN 
(
    SELECT
        CALC.Years,CALC.Months,D.Days,
        Result = RTRIM(Case When CALC.Years > 0 Then CONCAT(CALC.Years, ' year(s) ') Else '' End
                     + Case When CALC.Months > 0 Then CONCAT(CALC.Months, ' month(s) ') Else '' End
                     + Case When D.Days > 0 OR (CALC.Years=0 AND CALC.Months=0) Then CONCAT(D.Days, ' day(s)') Else '' End)
    FROM (VALUES(IIF(@Date1<@Date2,@Date1,@Date2),IIF(@Date1<@Date2,@Date2,@Date1))) T(StartDate, EndDate)
    CROSS APPLY(Select
        TempEndYear = Case When ISDATE(CONCAT(YEAR(T.EndDate), FORMAT(T.StartDate,'-MM-dd')))=1 Then CONCAT(YEAR(T.EndDate), FORMAT(T.StartDate,'-MM-dd'))
                        Else CONCAT(YEAR(T.EndDate),'-02-28') End
    ) TEY
    CROSS APPLY(Select EndYear = Case When TEY.TempEndYear > T.EndDate Then DATEADD(YEAR, -1, TEY.TempEndYear) Else TEY.TempEndYear End) EY
    CROSS APPLY(Select
        Years = DATEDIFF(YEAR,T.StartDate,EY.EndYear),
        Months = DATEDIFF(MONTH,EY.EndYear,T.EndDate)-IIF(DAY(EY.EndYear)>DAY(T.EndDate),1,0)
    ) CALC
    CROSS APPLY(Select Days =  DATEDIFF(DAY,DATEADD(MONTH,CALC.Months,DATEADD(YEAR,CALC.Years,T.StartDate)),T.EndDate)) D
)

and

Select * From dbo.FindDateDiff('2015-12-15','2018-12-14')

Result: