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