Trucos para el cálculo de diferencias de fechas en SQL Server

¿Problemas con el cálculo de fecha en SQL Server? A continuación un conjunto de trucos que te pueden ayudar en la caótica tarea de trabajar con fechas en SQL Server.

Truco 0:
Empezaremos con lo básico atacando el problema del formato de fechas. Lo usual es empezar consulta usando un char para representar la fecha, por ejemplo:

SELECT Id FROM Proveedores WHERE FechaRegistro = '02/10/1986'

Si SQL Server está configurado con otro idioma recibiremos el siguiente error:

La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Es decir, SQL Server espera MM/DD/YYYY y no DD/MM/YYYY, el cual es un problema común que quita la paciencia muy rápido. Antes de tirar por la ventana el servidor recomiendo intentar los siguiente:

a. Cambiar el idioma del login

Luego de iniciar la validación de usuarios ejecutar el siguiente procedimiento desde el cliente:

EXEC sp_defaultlanguage 'login', 'idioma'

donde login es el nombre del usuario de SQL Server e Idioma es el 'Idioma' disponible instalado en SQL Server que se puede obtener desde la tabla master.sys_language así:

SELECT * FROM master.sys_language

b. Antes de hacer la consulta establecer el idioma

Así:

SET LANGUAGE 'idioma'

Truco 1:
Calcular la diferencia entre dos fechas en SQL Server es facil usando la función DATEDIFF. Por ejemplo, para hallar la diferencia entre '2007-05-07' y '2007-05-08' en días tan solo basta ejecutar:

SELECT DATEDIFF(day, '2007-05-07', '2007-05-08');
Resultado:
----------
1 day

Truco 2:

Ok, tal vez ya conocías el truco anterior, déjame mostrar una combinación entre DATEADD (el inverso de DATEDIFF) y DATEDIFF para hacer los siguientes cálculos útiles :

Saber el primer y último día del mes:

 
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate()  )+1, 0));

Saber el primer día del año pasado:

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Saber el primer lunes del mes:

SELECT DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(DAY,getdate()),getdate())), 0);

Truco 3:

Sin embargo, DATEDIFF no es útil cuando queremos hacer el siguiente cálculo:

la diferencia en años, meses y dias entre 2009/02/02 y 2009/01/08 = "1 mes y 6 dias"
 
Es decir, dada dos fechas,  devolver la cantidad de dias, meses y años (string) que han pasado entre dos fechas. Un cálculo muy útil por ejemplo para un sistema de escalafón donde se debe averiguar el tiempo en años, meses y días exactos que el personal está laborando.

Aquí en Vida Amarilla hemos hecho una función escalar que hace el cálculo recibiendo una fecha y devuelve una cadena con la expresión dias, meses y años transcurridos. También debe hacer excepción de meses o días o años si son igual a 0.

Para probar la función:

select dbo.EntreFechasAñoMesDia('02/10/1987','02/02/2011')
 
Resultado:
---------
Pasan 23 años y 4 meses 

Validando el cálculo de fechas en Wolfram|Alpha nos muestra lo mismo:

23 years  4 months

Es Correcto.

Referencia:

1. DATEDIFF (Transact_SQL)
2. Obtener fechas en SQL Server
3. Script EntreFechasAñoMesDia de IISLAS

COMENTARIOS 18 Comentarios

18 Respuestas para : Trucos para el cálculo de diferencias de fechas en SQL Server

  1. Anónimo says:

    hola, muy buen aporte el que comentas, solo quería preguntar algo con respecto al último código que pusiste para calcular año, mes y día... quería saber si esto de igual forma se puede aplicar a MySQL 5.3??? o la variación de dicha función es demasiada...?

    de antemano muchas gracias y disculpad por las molestias, pero soy nuevo en esto...

  2. Anónimo says:

    como podria desarrollar un calculo de dias vividos en dev c++

  3. Anónimo says:

    tengo problemas con Epi Data cuando intento que me devuelva una fecha en algunos casos 19 años 6 meses y 36 dias cuando en realidad debiera devolver 19 años 7 meses y 6 dias , como puedo solucionar desde ya gracias

  4. Yo armé esta forma de conseguir la edad. Si encuentran errores me ayudarían mucho para poder actualizar mi código SQL.

    UPDATE MI_TABLA
    SET
    MI_EDAD =
    CASE
    WHEN DATEPART(MM, FEC_NACIMIENTO) = DATEPART(MM, GETDATE()) AND DATEPART(DD, GETDATE()) >= DATEPART(DD, FEC_NACIMIENTO)
    THEN DATEPART(YY, GETDATE()) - DATEPART(YY,FEC_NACIMIENTO)
    ELSE (DATEPART(YY,GETDATE()) - 1) - (DATEPART(YY, FEC_NACIMIENTO))
    END

    -- Explicación:
    En "WHEN" hago la consulta si el mes de nacimiento es igual al mes actual, luego verifico que el día sea igual o menor(osea dias anteriores) al día actual en curso. Si todo esto es verdadero se imprime en THEN la resta del año actual menos la fecha de nacimiento. Y el ELSE me dice que, si no se cumple nada de ello resto los años y le quito 1.

    Saludos!

  5. Anónimo says:

    Una forma menos complicada de hacer el calculo de anos, mes y dias transcurrido entre dos fechas es esta, entendiendo que cada cual la pueda adaptar a sus necesidades.

    Declare @fec1 as datetime, @fec2 as datetime,
    @anos as int, @meses as int, @dias as int

    set @fec1 = '20120101'
    set @fec2 = GETDATE();

    set @anos = DATEDIFF(YYYY,@fec1, @fec2)
    set @fec1 = DATEADD(YYYY,@anos, @fec1)
    set @meses = DATEDIFF(mm,@fec1, @fec2)
    set @fec1 = DATEADD(mm,@meses, @fec1)
    set @dias = DATEDIFF(dd,@fec1, @fec2)

    select @anos as anos, @meses as meses, @dias as dias

  6. Si yo quisiera saber el primer miércoles de cada mes, como podría hacerlo?

  7. Anónimo says:

    Declare @lFec1 as datetime, @lFec2 as datetime

    set @lFec1 = '20030814'
    set @lFec2 = '20090801'

    Declare @anos as int, @meses as int, @dias as int, @ld as datetime

    If @lFec1<@lFec2
    Begin
    set @Anos = datepart(yyyy, @lFec2)- datepart(yyyy,@lFec1)
    set @meses = datepart(mm,@lFec2)- datepart(mm,@lFec1)
    set @Dias = datepart(dd,@lFec2)- datepart(dd,@lFec1)

    If @Dias<0 Begin
    set @meses=@meses -1
    set @ld = dateadd(mm, 1, dateadd(mm,-1,@lFec2))
    set @ld = dateadd(DD,-datepart(dd,@ld),@ld)
    set @Dias = @dias + DATEPART(DD,@ld)
    End

    If @meses<0 Begin
    set @Anos = @Anos -1
    set @meses = 12 + @meses
    End

    select @anos as años, @meses as meses, @dias as dias
    End

  8. Hola como estas ? mira tengo dos problemas significativos,uso el datediff en una tabla para que me diga cuantos dias paso alguien por una habitacion,el resultado esta bien solo que cuando quiero pasar ese resultado a otra tabla donde hago las liquidaciones correspondientes no me muestra el resultado del datediff de la otra tabla.ya trate con innerjoin y con left join lo que hace que empeoren las cosas ya que no muestra ni una de la sotras consultas de la tabla .
    jeje espero aver sido claro el otro problema lo dejo para despues para no ser tan pesado .
    saludos y gracias desde ya a alguna sugerencia ya que este drama hara que tire este proyecto al tacho de papeles .

  9. Anónimo says:

    Hola buenas, como puedo colocar la sentencia para que me busque los ultimos 6 meses.

  10. Anónimo says:

    hola, como puedo obtner la diferencia en dias entre dos fechas sin la funcion datediff que no la soporta la version de mysql

    Gracias

  11. Estimado muy buenos tus articulos pero: POR DIOS QUITA ESA BARRA DE LA INZQUIERDA (TWIYYER, FACEBOOK, ETC) que es molesta e impide leer bien. por lo demas todo excelente, pero esa barra flotante es muy mala idea.

  12. Quién me podría ayudar a Conseguir la siguiente consulta:
    Obtener los nombres de la tabla Datos que se ingresaron en la fecha 'X' a la fecha 'Y', pero hay que tener en cuenta que en esa tabla Datos solo existe 1 atributo de Fecha que es de tipo datetime, no hay 2 que digan fecha1 y fecha2... ojo!!! lo necesito en storeprocedure... Les agradecería mucho... A ver quién me ayuda... Muchas bendiciones a todos

  13. Anónimo says:

    ayudenme como hacer esta operacion en sql acerca de funciones

    Crear una función que calcule la antigüedad de un empleado. (PARAMETRO DE ENTRADA: Fecha de ingreso)

  14. create PROCEDURE SACADIFEENMESES

    @FECHAACTUAL DATE

    AS

    declare @FechaIngreso AS date,
    @FechaEgreso AS date

    select @FechaEgreso = GETDATE()

    Select
    DATEDIFF(mm, @FECHAACTUAL, @FechaEgreso) AS Meses,
    DATEDIFF(dd, @FECHAACTUAL, @FechaEgreso) AS Dias,
    DATEDIFF(yy, @FECHAACTUAL, @FechaEgreso) AS Años

  15. Muy buena la función compartida en el truco 3, solo que el encontré un pequeño error y lo comparto para que puedan corregirla.

    La sentencia

    SET @Dias = (DAY(DATEADD(mm,1,CAST(('01/' + STR(@MesInicio) + '/' + STR(@AñoInicio)) AS DATETIME)) - 1 )- @DiaInicio) + @DiaFin

    Debe ser reemplazada por las dos líneas siguientes, previa declaración del a variable @DiaFinMesPrevio en la sección donde se declaran las variables dentro de la función (DECLARE @DiaFinMesPrevio INT)

    SET @DiaFinMesPrevio = DAY(CAST('01/' + STR(@MesFin) + '/' + STR(@AñoInicio) AS DATETIME) - 1)
    SET @Dias = (CASE WHEN @DiaFinMesPrevio > @DiaInicio THEN @DiaFinMesPrevio - @DiaInicio ELSE 0 END) + @DiaFin

    Esta corrección soluciona el problema que se genera cuando la fecha de inicio tiene una cantidad de días mayor a la fecha de fin y la fecha de inicio corresponde a un mes de menos de 31 días. Para ver más claramente el error les recomiendo probar la función original calculando la diferencia entre 30/04/2013 y 31/05/2013 y verán que les da la misma diferencia que la que se obtiene entre 30/04/2013 y 01/06/2013 (en ambos casos la función original arroja una diferencia de “Pasa 1 mes y 1 día”). En cambio sí realizan el cambio descripto arriba la función arrojará una diferencia de “Pasa 1 mes y 1 día” entre las fechas 30/04/2013 y 31/05/2013 y una diferencia de “Pasa 1 mes y 2 días” entre las fechas 30/04/2013 y 01/06/2013.

    Espero que les sea útil mi aporte.

  16. Anónimo says:

    perdón me dice que el nombre del objeto no es valido

Deja un comentario