Wie zu verwenden DATEDIFF zurückzukehren, Jahr, Monat und Tag?

Wie kann ich mit DATEDIFF zurück die Differenz zwischen zwei Datumsangaben in Jahren, Monaten und Tagen, die in SQL Server 2005

DATEDIFF (date , Datum)

Wie Ergebnis: 2 Jahr 3 Monat 10 Tage

Kann jemand vervollständigen dieses 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

23year 280month 8506day ?
Beachten Sie, dass im Allgemeinen, die Frage ist schlecht definiert. Betrachten wir zwei Termine, in den letzten Speicher: 2009-02-25 2009-03-28-und. Mein Verdacht ist, dass die zu erwartende Antwort wäre 0 Jahren, 1 Monat, 3 Tage. Was 2009-02-25-und 2009-03-05? Und 2009-03-25-und 2009-04-05? 2009-02-25-und 2009-04-05? Die erste davon sein könnte, sein soll, 0y, 0m, 8d; der zweite könnte sein, 0y, 0m, 11d; ich bin nicht sicher über die Dritte - wohl 0y, 1m, 11d. Was 2000-02-29 und 2003-01-31? 2000-02-29 und 2003-02-28?

InformationsquelleAutor monkey_boys | 2009-10-09

Hier ist meine Lösung für Eric Funktion:

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

Gute Entscheidung, auf den Einsatz von ABS zu behandeln, wenn das Startdatum liegt nach dem Enddatum.

Diese:

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

..oder nicht-CTE verwenden Sie für SQL Server 2000 und vor:

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

...zurück:

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

Referenz: DATEDIFF

DATEDIFF(dd, t.start_date, t.end_date), dass alle wieder Tag ? nicht subtrahieren ?
nicht die richtige Antwort?
DATEDIFF-gibt die Differenz basiert auf der datepart-Wert für die zur Verfügung gestellten Daten als int. IE: JJ heißt Jahr, also DATEDIFF(yy, t.start_date, t.end_date) wird wieder die Anzahl der Jahre zwischen der vorgesehenen Termine. Sie können immer testen Sie es auf Ihrem system, um sicher zu sein.
Msg 245, Ebene 16, Status 1, Zeile 1 Fehler bei der Konvertierung beim konvertieren den varchar-Wert 'Jahr' in den int-Datentyp.
17-205 Monat 6239day ??
Convert(varchar(10),DATEDIFF(yy,neue.BIRTHDATETIME,neue.VISITDATETIME)) +'Jahr' + Convert(varchar(10),DATEDIFF(mm,neu.BIRTHDATETIME,neue.VISITDATETIME)) +' Monat ' + Convert(varchar(10),DATEDIFF(dd,neue.BIRTHDATETIME,neue.VISITDATETIME)) +'Tag' ALS Alter
Funktioniert nicht über die Schaltjahre - versuchen 2011-09-30 zu 2011-09-29 gibt "1 Jahr 0 Monat 1 Tag"

InformationsquelleAutor OMG Ponies

3

Funktioniert es für manche Situationen, aber wenn Sie subtrahieren von Daten wie 2007-10-15 (DateHired) von 2011-01-13 (dateterminated), gibt Sie eine negative Zahl ein, wenn das abs nicht herum, sondern setzen die abs-around nicht fix it entweder, weil dann das Jahr und der Monat Abbildung sind falsch.

InformationsquelleAutor L Harrison

Die Modifizierte Funktion

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

InformationsquelleAutor gngolakia

Prüfen Sie auf dieser Seite... http://www.sqlteam.com/article/datediff-function-demystified

Erstellen dieser Funktionen:

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

und

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

und schließlich

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

Größen!

InformationsquelleAutor fbn

Erstellen, diese Funktion wird es geben, das genaue Datum Differenz wie Jahre Monate Tage

    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

Dies gibt die korrekte DAUER zwischen den Zeitpunkten 2015-01-24-und 2017-10-17, die die anderen Funktionen scheinen nicht zu. Sie geben alle 7 Tage , was ist der Unterschied zwischen 24 und 17..falsch! ...vielen Dank 🙂

InformationsquelleAutor Rajesh Garg

Ich weiß, es gibt ein paar Antworten hier schon, aber ich dachte, ich würde hinzufügen, was ich kam mit, da (zumindest für mich) scheint es sehr einfach zu Folgen:

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
    )

Dann, wenn Sie anrufen mit:

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

Bekommst du dieses Ergebnis:

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

Natürlich könnten Sie dies ändern, um eine formatierte Ausgabe und eine Skalare variable ersetzt werden, aber das überlasse ich Euch 🙂

EDIT:

Habe ich beendet, brauchen auch eine Zeit-Funktion geben Sie ein format wie "5 Jahre und vor 2 Tagen"

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

Hier sind 2 Beispiele

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

und

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

Ich hoffe das ist für jemanden in der Zukunft sowieso, wie ich konnte nicht viel auf so etwas, wenn die Suche (könnte nur einer von diesen Tagen, aber ich bin offen für Verbesserungen, wie ich weiß, ich bin nicht immer die kompakteste und Schnellste code-Programmierer 🙂

Hinsichtlich

Liam

Diese Antwort ist die nächste, aber wählen Sie * aus [dbo].[fn_DateDiff_YMDMHS]('2017-07-10 18:07:02.857', '2017-08-09 15:11:51.963') Ergebnis : 1 Monat -1 -3 Tage Stunden

InformationsquelleAutor Liam Wheldon

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

Sie sind Antworten auf eine 7 Jahre alte Frage, die hat schon eine Reihe von Antworten darauf. Sie Bearbeiten Ihre Frage einige Kommentare, die erklären, was dieser code tut, und warum es verbessert auf, was bereits hier ist.

InformationsquelleAutor xzmilan

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
)

und

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