首页 文章

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

提问于
浏览
-1

如何使用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 回答

  • 0

    这是我对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

  • 3

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

  • 2

    修改后的功能

    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
    
  • 1

    查看此页面... 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
    

    巨星!

  • 4

    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
    
  • 3

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

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

相关问题