如何使用DATEDIFF返回年,月和日?

如何使用DATEDIFF返回SQL Server 2005中的年,月和日两个日期之间的差异

DATEDIFF(日期,日期)

如何结果:2年3个月10天

谁能完成这个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

回答(8)

2 years ago

这是我对Eric功能的解决方案:

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

如果开始日期在结束日期之后,请使用ABS来处理 .


这个:

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

..或非CTE用于SQL Server 2000和之前:

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

...将返回:

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

参考:DATEDIFF

2 years ago

它适用于某些情况但是当你从2011-01-13(dateterminated)减去2007-10-15(DateHired)等日期时,它会给你一个负数,当abs不在它周围而是把abs放在一边不会修复它,因为那么年和月的数字是不正确的 .

2 years ago

修改后的功能

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

2 years ago

查看此页面... http://www.sqlteam.com/article/datediff-function-demystified

创建此功能:

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

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

最后

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

巨星!

2 years ago

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

2 years ago

我知道这里已经有了一些答案,但我想我会添加我想出的内容(至少对我来说),这似乎很简单:

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
    )

然后当你打电话:

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

你会得到这个回复:

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

显然你可以将它更改为格式化输出并改为使用标量变量,但我会留给你:-)

编辑:

我最后还需要做一段时间的功能来返回像“5年和2天前”这样的格式

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

这是两个例子

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

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

我希望这对未来的某个人有用,因为我在搜索时找不到这样的东西(虽然可能是那些日子之一)但我愿意改进,因为我知道我并不总是最紧凑或最快的代码程序员:-)

问候

利亚姆

2 years ago

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

2 years ago

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
)

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

结果:

enter image description here