Te presento mi nuevo sitio web: "El Futuro de los Datos"

Aunque SQL Server Si!, seguirá activo, iré bajando la frecuencia de publicación.
Si quieres conocer todas las novedades que vaya publicando, te recomiendo que lo visites y te suscribas. Tengo un regalito para mi audiencia:

Tu primer Dashboard en "piloto automático" listo en 30 minutos
Sólo para suscriptores.
.

30 dic. 2008

Tratamiento de fechas y horas en SQL Server

Introducción.

El tratamiento de fechas en SQL Server es uno de temas que más preguntas generan en los foros y grupos de noticias. SQL Server tiene los tipos de datos datetime y smalldatetime para almacenar datos de fecha y hora.

No hay tipos de datos diferentes de hora y fecha para almacenar sólo horas o sólo fechas. Si sólo se especifica una hora cuando se establece un valor datetime o smalldatetime, el valor predeterminado de la fecha es el 1 de enero de 1900. Si sólo se especifica una fecha, la hora será, de forma predeterminada, 12:00 a.m. (medianoche), es decir, las 00:00.

Nota Importante: En SQL Server 2008 sí que tenemos como novedad tipos de datos para almacenar sólo la fecha y sólo la hora. Por tanto todo lo que contamos a continuación se puede solucionar usando los nuevos tipos de datos.


Tipo de datos Datetime.

Datos de fecha y hora comprendidos entre el 1 de enero de 1753 y el 31 de diciembre de 9999, con una precisión de un trescientosavo de segundo, o 3,33 milisegundos.

SQL Server rechaza todos los valores que no puede reconocer como fechas entre 1753 y 9999.


Tipo de datos Smalldatetime.

Datos de fecha y hora desde el 1 de enero de 1900 al 6 de junio de 2079, con precisión de minutos. Entonces si se utiliza un valor smalldatetime los segundos y milisegundos son siempre 0.

Diferencia entre Datetime y Smalldatetime.

SQL Server almacena internamente los valores de tipo de datos datetime como enteros de 4 bytes y los valores smalldatetime como enteros de 2 bytes.


Funciones de fecha y hora (Transact-SQL)

Estas funciones escalares realizan una operación sobre un valor de fecha y hora de entrada, y devuelven un valor de cadena, numérico o de fecha y hora:

DATEADD
DATEDIFF
DATENAME
DATEPART
GETDATE
DAY
MONTH
YEAR


Trabajando con fechas ...

Con algunos pequeños ejemplos trataremos de resolver los mayores problemas para trabajar con estos tipos de datos. Para ello utilizaremos las funciones CAST y CONVERT.

Separando Fecha y Hora.

Declare @Fecha datetime
Set @Fecha = Getdate()
Select Convert(Char(10), @Fecha,112) As SoloFecha, Convert(Char(8), @Fecha, 108) As SoloHora
SoloFecha SoloHora
---------- --------
20010803 07:35:02
(1 row(s) affected)

Otra forma de conseguir el mismo resultado:

Declare @Fecha datetime
Set @Fecha = Getdate()
SELECT Convert(varchar, @Fecha, 3) AS SoloFecha, Convert(varchar, @Fecha, 8)

Operaciones con Fechas (diferencia entre dos fechas).

Obtener diferencia de meses, dias, minutos, etc. entre dos fechas.

Para realizar operaciones entre dos fechas MSSQL tiene la función DATEDIFF. Veamos algunos ejemplos de cómo utilizarla:

declare @FechaIngreso datetime
declare @FechaEgreso datetime

select @FechaIngreso = '19981231 15:15'
select @FechaEgreso = '20021005 10:10'

Select
DATEDIFF(dd, @FechaIngreso, @FechaEgreso) AS Dias,
DATEDIFF(mm, @FechaIngreso, @FechaEgreso) AS Meses,
DATEDIFF(mi, @FechaIngreso, @FechaEgreso) AS Minutos

Para obtener otras diferencias podemos recurrir a la siguiente tabla:

Parte de la fecha Abreviaturas
año aa, aaaa
trimestre tt, t
mes mm, m
día del año da, a
día dd, d
semana sm, ss
hora hh
minuto mi, n
segundo ss, s
milisegundo Ms

Otro ejemplo de DATEDIFF en donde recuperados los datos de la ultima semana partiendo de la fecha del día:

SELECT TusDatos
FROM TuTabla
WHERE
DATEDIFF(dd, TuFecha, GetDate()) <= 7

Continuando con las operaciones con las fechas, veamos como podemos hacer para sumar, restar, días, minutos, meses, a una fecha, para ello utilizamos la función DATEADD:

select convert(varchar(12), DATEADD(month, -1, getdate()), 106)
as 'un mes atrás'
select convert(varchar(12), DATEADD (week, -1, getdate()), 106)
as 'una semana atrás'
select convert(varchar(12), DATEADD (day, -1, getdate()), 106) as 'ayer'

Sugerencia:

Estos ejemplos que mostramos a continuación devolverían el mismo resultados que las consultas anteriores, pero, si, siempre hay un pero.... hace un tiempo nuestro compañero Fernando Guerrero me sugirió no utilizarlo pues este truco no está soportado oficialmente por SQL Server ni por el estándar ANSI.

select convert(varchar(12), getdate()-7), 106) as 'una semana atrás'
select convert(varchar(12), getdate()-1), 106) as 'Ayer'

Funciona, pero no sabemos hasta cuando.


Ampliar información:

Puede consultar en los B.O.L. (Books OnLine - Libros en Pantalla) cualquiera de las instrucciones citadas anteriormente.

También puede consultar los artículos y ejemplos publicados en http://www.portalsql.com
Allí busque la palabra 'fechas' y obtendrá todos los artículos publicados sobre el tema.

12 comentarios:

Ivich dijo...

Me gustaría saber como resolverías la siguiente cuestión porque tengo dudas.
Necesito obtener la diferencia entre dos fechas pero sólo en horas, minutos y segundos. Mi solución ha sido esta:
CONVERT (VARCHAR(10), { fn MOD(DATEDIFF(HOUR, dbo.PARADAS.TIME_BEGIN_PARADA, dbo.PARADAS.TIME_END_PARADA), 24) }) + ' : ' + CONVERT (VARCHAR(10), { fn MOD(DATEDIFF(MINUTE, dbo.PARADAS.TIME_BEGIN_PARADA, dbo.PARADAS.TIME_END_PARADA), 60) }) + ' : ' + CONVERT (VARCHAR(10), { fn MOD(DATEDIFF(SECOND, dbo.PARADAS.TIME_BEGIN_PARADA, dbo.PARADAS.TIME_END_PARADA), 60) })

Esto funciona para sqlserver 2005 pero necesitaría que en casos en los que sea < 10 el valor obtenido, pues que aparezca del siguiente modo. 09:03:04 y no como aparece ahora en algunas ocasiones con 9:3:4

Para ello se me ha ocurrido esta solución: IF ((MOD(DATEDIFF(HOUR, dbo.PARADAS.TIME_BEGIN_PARADA, dbo.PARADAS.TIME_END_PARADA), 24)) < 10 )
{'0'+CONVERT(VARCHAR(10),{ fn MOD(DATEDIFF(HOUR, dbo.PARADAS.TIME_BEGIN_PARADA, dbo.PARADAS.TIME_END_PARADA), 24) })}
ELSE {CONVERT(VARCHAR(10),{ fn MOD(DATEDIFF(HOUR, dbo.PARADAS.TIME_BEGIN_PARADA, dbo.PARADAS.TIME_END_PARADA), 24) })} Pero no se porque motivo falla.

Salvador Ramos dijo...
Este comentario ha sido eliminado por el autor.
Salvador Ramos dijo...

Hola Ivich,

Yo aplicaría lo explicado en el post, agregando una multiplicación de días por las horas que tiene un día (x24), y de meses por días. Así multiplicando por la unidad inferior lo puedes llevar al formato que desees.

Un saludo
Salvador Ramos

Christian dijo...
Este comentario ha sido eliminado por el autor.
Christian dijo...

Hola,
Tengo un gran problema...trabajo en una empresa de desarrollo internacional, donde las aplicaciones validan el Time Zone correspondientes. Los desarrolladores base, me dicen que debo almacenar todas las fechas y horas en formato de hora estandar y no usar el DST. He investigadado bastante y no logro conseguir el método.
Puede alguien ayudarme con esto?

Gracias de antemano.

Salvador Ramos dijo...

Hola Cristian,

Tu pregunta, las aclaraciones a tus necesidades y las propuestas de soluciones, una vez conocidas con detalle tus necesidades, podrían dar lugar a un hilo largo. Creo que este no es el sitio apropiado.

No pienses por ello que intento eludir tu pregunta, te invito a que la propongas en el foro público de SQL Server, donde tanto yo, como el resto de participantes intentaremos ayudarte. Puedes acceder a él desde el siguiente link:
http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/threads

Un saludo
Salvador Ramos

Salvador Ramos dijo...

Hola Cristian,

Entiendo que esta cuestión daría lugar a un hilo con diversas cuestiones, respuestas, aclaraciones y demás, por lo que entiendo que este no es el sitio apropiado.

No pienses por ello que intento eludir tu pregunta, te recomiendo que la hagas en los foros públicos, donde tanto yo como el resto de participantes intentaremos ayudarte, aquí te dejo el link:
http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/threads

Un saludo
Salvador Ramos

Jhunior Fredy Caururo Tamara dijo...

Hola quiero restar dos fechas por ejem. Fechafinal - fechainicial (12/05/2015 - 15/03/2015) y la repuesta quiero en formato numerico o general (al restar en excel saldria 58). Dichas fechas al subir al sql estan en formato varchar. La sentencia k estoy usando es (Update productos set valida = fechafinal - fechainicial) pero me sale el mensaje operador no válido para el tipo fe dato. Operador subtract, tipo varchar. cual seria la solución Gracias.

Jhunior Fredy Caururo Tamara dijo...

Hola quiero restar dos fechas por ejem. Fechafinal - fechainicial (12/05/2015 - 15/03/2015) y la repuesta quiero en formato numerico o general (al restar en excel saldria 58). Dichas fechas al subir al sql estan en formato varchar. La sentencia k estoy usando es (Update productos set valida = fechafinal - fechainicial) pero me sale el mensaje operador no válido para el tipo fe dato. Operador subtract, tipo varchar. cual seria la solución Gracias.

Yuri Alexander Vargas L. dijo...

hola necesito una ayuda, tengo una BD en sql server 2000, lo registros tiene fecha(datetime) del 2012 , quisiera que me ayuden en la consulta como sumarle 3 años a la fecha 2015, y guardarlo en la misma bd sin alterar los meses, dias minutos y horas
----------------------------------
fecha
2012-01-02 13:42:36.000
-----------------------------------
fecha
2015-01-02 13:42:36.000

gracias

Armando gn dijo...

Gracias, es muy buen blog.

Luis Mauricio Donoso Proaño dijo...

Buenos días Salvador espero me puedas ayudar tengo el siguiente problema, trabajo con un programa llamado a3ERP y la base de datos es de SQL server, en el programa tengo creado unos campos llamados.
Campos y ejemplo:
Fecha inicio: 11/05/2016 formato DateTime
Hora inicio: 8:00 formato Varchar 5
Fecha final: 12/05/2016 formato DateTime
Hora final: 3:00 formato Varchar 5
Resultados:
Total jornada: 19:00 formato Varchar 25
Jornada laboral: 8:00 formato Varchar 5
Total horas extras: 11:00 formato Varchar 25
Me podrías ayudar con esto.
Un saludo gracias…

Google