Esta pregunta debe ser una de las que mas se me hacen y las que respondo en los diferentes foros o News, por tal motivo me he concentrado en tratar de escribir este artículo que tratará de explicar como usar las Fechas en Sql Server y que cosas debemos tomar en consideración.
Antes de empezar voy a tratar de explicar que son las Fechas para SqlServer. El Sql Server tiene básicamente dos tipos de datos donde se pueden almacenar fechas propiamente dichas, Datetime y SmallDateTime. En este cuadro veremos las diferencias entre estos dos tipos de Datos.
DateTime | Valores de Fecha y Hora que están comprendidos entre 1/1/1763 y 31/12/9999. La hora se expresa con una exactitud de hasta 1/300 de segundo |
SamallDateTime | Valores de Fecha y Hora que están comprendidos entre 1/1/1900 y 6/6/2079. El grado de precisión de la hora llega hasta el minuto |
Bien ahora conocemos los tipos de datos de nuestro motor, pero la gran pregunta, ¿Cómo Guarda internamente Sql Server las Fechas? ¿Lo hace en formato MM/DD/YYY o lo hará en DD/MM/YYYY?, bueno lamento decirles que nuestro motor siempre guarda las fechas de una sola forma y no esta referida a ningún formato (Americano, Español, Japonés ) como recién mencione
¿Entonces? Bueno Sql Server guarda las fechas DateTime como enteros de 4 Bytes (Los primeros 4 Bytes almacenan la Fecha y los otros 4 Bytes la Hora), en SmallDatetime como tienen menor precisión en lugar de ser dos grupos de 4 son dos grupos de 2.
Bien ahora sabemos también no solo que tipos de datos tenemos para guardar nuestras fechas y horas sino que también como las guarda el motor realmente.
Con todo esto me estoy preguntando, ¿Entonces porque cuando hago algún Select me trae las fechas en un formato X (como por ejemplo el Americano)?
Claro esto es así porque leer las fechas en grupos de Bytes no es muy elegante que digamos y nuestros usuarios no estarían muy contentos.
Bueno ahora entonces tenemos un dilema, ya que cuando quiera hacer una consulta por ejemplo entre fechas deba primero saber que tipo de Formato usar (Americano, Español, etc) ya que no es lo mismo 10-01-2004 (donde 10 es el día y 01 el mes) a 10-01-2004 (Donde 10 es el mes y 01 el día).
Una de las técnicas que veo que mas se usan es aplicar siempre un formato y con eso parece estar la cosa controlada, claro que no es para nada así ya que el formato dependerá de que idioma tenga definido nuestro usuario por lo cual esto nos puede traer muchas complicaciones.
Entonces, ¿Cómo hacer una consulta de fechas en un formato y no morir en el intento?, bueno acá hay una solución muy interesante, y es el uso del Standard ANSI para las fechas, este Standard esta compuesto así: YYYYMMDD HH:mm:ss’ , si yo empiezo a utilizar este formato para mis consultas no tendré problemas en ninguna de mis aplicaciones por mas que nuestro usuario este configurado en Ingles, Español o lo que fuere, ni tampoco si en un Windows tengo definido en su configuración regional cualquier cosa.
Ahora tenemos un poco de teoría, pero la misma debe ir acompañada con alguna práctica no? Las cosas hay que demostrarlas para que nos convenzan, por lo menos es mi forma de hacer y pedir las cosas.
Para nuestro ejemplo utilizaremos la Base de Datos Northwind que ya viene como ejemplo en nuestro Sql Server, de no tenerla favor de instalarla desde el instalador correspondiente.
/* Creamos un usuario donde su idioma predefinido es el Español */ sp_addlogin 'UserFechas','pepe','master','Español' /* vamos a darle acceso a nuestra Base de Datos Northwind */ use Northwind GO sp_grantdbaccess 'UserFechas' GO
Ahora salimos de nuestro QA (Analizador de Consultas) y volveremos a entrar logiandonos con este nuevo usuario que creamos (Recuerden que la autentificación de nuestro motor en este caso debe ser Mixta y que el uso de cuentas de SqlServer no es aconsejable, sino que hay que tratar de utilizar la autentificación Windows, pero para hacer el ejemplo mas fácil es que use Autentificación Sql con un usuario Sql)
Bien lo primero que haremos es verificar que el idioma este en español como lo hemos indicado anteriormente al usuario, para ello usaremos la siguiente instrucción.
Select @@Language
Este debería ser el resultado luego de ejecutar dicha consulta.
--------------------------------------------------------- Español (1 filas afectadas)
Bien ahora usaremos la tabla Orders y haremos la consulta de fechas de dos formas distintas, la primera usando los formatos como la mayoría esta acostumbrado y la segunda utilizando el formato ANSI que seria el correcto.
use northwind go -- Opcion que estamos acostumbrados a usar select count(*) from orders where orderdate >='01-08-1997' -- Opcion con Ansi select count(*) from orders where orderdate >='19970801'
Bien acá veremos que en ambas consultas nos trae como retorno del Count 460, esto quiere decir que encontró 460 registros que su orderdate son mayor o igual a el 1 de Agosto de 1997.
Bien ahora cambiaremos el lenguaje de nuestra sesión a ingles y haremos la misma consulta para ver que sucede:
use northwind go /* Cambiamos el lenguaje para esta sesión a Ingles, esta opción no cambia el lenguaje del Usuario sino que solo la sesión que al cerrarla volverá a tomar la del usuario si Es que no especifico ningún SET LANGUAGE */ SET LANGUAGE us_english GO -- Opción que estamos acostumbrados a usar select count(*) from orders where orderdate >='01-08-1997' -- Opción con ANSI select count(*) from orders where orderdate >='19970801'
Como vemos aquí la primer consulta paso de los 460 registros a los 670, porque sucede esto? Bueno como es ingles ahora la fecha que queremos consultar es realmente
Mayor al 8 de enero de 1997, pero ejecuten la segunda consulta y verán que esta sigue retornando los 460 originales, pues bien esto es por todo lo que hemos dicho antes y porque es muy pero muy importante utilizar este tipo de formato (YYYYMMDD hh:mm:ss)
Bien hemos aprendido a usar el formato ANSI para nuestras consultas pero eso no es todo, ahora trataremos de ver algunos ejemplos típicos de búsquedas con fechas para poder analizar bien estos casos.
Una consulta muy recurrente es: ¿Cómo busco los registros de una fecha en particular ya que me toma también la hora? Bueno esto es totalmente cierto, si ponemos por ejemplo = ‘20040101’ esto traerá los registros no del día 01 de enero del 2004 totalmente sino aquellos que sean de las 0 Horas, bueno para solucionar esto a mi me gusta usar esta consulta muy simple.
Select * from orders Where orderdate >='19970805' and orderdate < dateadd(dd,1,'19970805')
Como verán primero es que sigo en la misma regla del ANSI y luego utilizo la función Dateadd para poder a la fecha sumarle un día, con esto logro que no me importe que hora tengan los registros que me traerá todos los del día 05 de agosto de 1977.
Bien ahora tenemos casi el 90% resuelto de los problemas más habituales con fechas, pero de todos modos no deberíamos dejar de ver las funciones que están asociadas con la manipulación de Fechas
Función | Determinismo |
DATEADD | Devuelve un valor datetime nuevo que se basa en la suma de un intervalo a la fecha especificada. |
DATEDIFF | Devuelve el número de límites de fecha y hora que hay entre dos fechas especificadas. |
DATENAME | Devuelve una cadena de caracteres que representa la parte de la fecha especificada de la fecha especificada. |
DATEPART | Devuelve un entero que representa la parte de la fecha especificada de la fecha indicada. |
DAY | Devuelve un entero que representa la parte del día de la fecha especificada. |
GETDATE | Devuelve la fecha y hora actuales del sistema en el formato interno estándar de Microsoft® SQL Server™ para los valores datetime. |
GETUTCDATE | Devuelve el valor de datetime que representa la hora UTC actual (Universal Coordinated Time u hora del meridiano de Greenwich). La hora UTC actual se deriva de la hora local actual y la configuración de zona horaria del sistema operativo del equipo en el que se ejecuta SQL Server. |
MONTH | Devuelve un entero que representa el mes de una fecha especificada. |
YEAR | Devuelve un entero que representa la parte de año de la fecha especificada. |