operaciones - obtener mes y año de una fecha sql

¿Cómo usar DATEDIFF para devolver año, mes y día?

Question

¿Cómo puedo usar DATEDIFF para devolver la diferencia entre dos fechas en años, meses y días en SQL Server 2005?

DATEDIFF (fecha, fecha)

Cómo dar como resultado que: 2 año 3 mes 10 día

¿Alguien puede completar este 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

Answer 1

Aquí está mi solución a la función de 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''
)

Buena llamada sobre el uso de ABS para manejar si la fecha de inicio es posterior a la fecha de finalización.

Esta:

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

..o no CTE usando para SQL Server 2000 y anteriores:

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

... volverá:

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

Referencia: DATEDIFF

Answer 2

Consulte esta página ... http://www.sqlteam.com/article/datediff-function-demystified

Crea estas funciones:

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

y

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

y finalmente

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

¡Grandes!

Answer 3

Funciona para algunas de las situaciones, pero cuando resta fechas como 2007-10-15 (DateHired) desde 2011-01-13 (fecha de finalización) le da un número negativo cuando los abdominales no están alrededor de él, pero poniendo los abdominales alrededor tampoco lo soluciona porque la cifra de año y mes es incorrecta.

Answer 4

Cree esta función, le dará la diferencia de fecha exacta como el año meses días

    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

Answer 5

La función modificada

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

Answer 6

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

Answer 7

Sé que ya hay algunas respuestas aquí, pero pensé que agregaría lo que se me ocurrió ya que (al menos para mí) parece muy simple de seguir:

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
    )

Luego cuando llamas con:

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

Obtendrás esto devuelto:

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

Obviamente, podría cambiar esto a una salida formateada y tener una variable escalar en su lugar, pero se lo dejo a usted :-)

EDITAR:

Terminé necesitando también hacer una función de tiempo atrás para devolver un formato como "5 años y 2 días atrás"

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

Aquí hay 2 ejemplos

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

y

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

Espero que esto sea de utilidad para alguien en el futuro de todos modos, ya que no pude encontrar mucho sobre esto cuando busqué (podría ser uno de esos días). Estoy abierto a las mejoras ya que sé que no soy siempre el programador de código más compacto o más rápido :-)

Saludos

Liam