SQL Server 2008: DATE, TIME, DATETIME2, DATETIMEOFFSET

Con la llegada de SQL Server 2008, algunos de los nuevos tipos de datos que ya son (o serán) novedades tenemos a los siguientes: HIERARCHY ID (para almacenar valores de nodos en un jerarquía), FILESTREAM (para almacenar data de tipo grande, documentos, imágenes), tipos para almacenar datos espaciales como GEOGRAPHY, GEOMETRY, así como los tipos de datos TIME (3 a 5 bytes de tamaño) y DATE (3bytes), DATETIME2 (6 a 8 bytes), DATETIMEOFFSET (8 a 10 bytes), entre otros más.

En este post vamos a hablar acerca de los tipos de datos para manejar fechas y tiempo, es decir, los 4 últimos arriba mencionados. Los otros tipos de datos serán motivos de otro post!.

DATETIME es un tipo de dato compuesto que almacena fecha y tiempo. La novedad es que en SQL Server 2008, el tipo de dato DATETIME se ha separado en dos: DATE y TIME, con esto no quiero decir que DATETIME se haya eliminado, este sigue siendo necesario para otros escenarios, pocos pero en fin. Esta noticia es muy buena ya que por ejemplo, siempre, casi siempre... sólo necesitabamos almacenar la fecha, más no el tiempo, y el único tipo de dato que podiamos usar era DATETIME, el cual "amablemente" guardaba algo más: el tiempo, aunque no lo necesitemos, pues no habia solución. DATETIME aparte de ser un tipo de dato cuyo tamaño es 8 bytes (realmente bastante), es pesado pudiendo originarse problemas de performance general.

Particularmente casi nunca uso DATETIME, prefieron usar con mucho criterio SMALLDATETIME, Porqué?, pues su tamaño es 4 bytes, y para lo que necesito está bien, puedo manejar fechas entre el rango de 1900 y 1979. Obviamente, SMALLDATETIME también almacena la hora, cosa que no se necesita en la mayoria de aplicaciones. De manera semejante sucedía cuando queriamos trabajar sólo con la hora, no había forma de separar la hora de la fecha. Ejemplo:

-- creando un tipo de datos DATE, y asignándole un valor
DECLARE @FechaTiempo DATETIMESET
@FechaTiempo= GETDATE()
SELECT @FechaTiempo AS FechaTiempo


Ejemplos utilizando la función DATEDIFF y DATEADD
*////////////////////////////////////////////////////////////////*
*/ El primer dia del mes:
*////////////////////////////////////////////////////////////////*
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

*////////////////////////////////////////////////////////////////*
*/El Ultimo dia del mes:
*////////////////////////////////////////////////////////////////*
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0));

*////////////////////////////////////////////////////////////////*
*/ El Lunes de la semana actual:
*////////////////////////////////////////////////////////////////*
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

*////////////////////////////////////////////////////////////////*
*/ Primer dia del año:
*////////////////////////////////////////////////////////////////*
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

*////////////////////////////////////////////////////////////////*
*/ Ultimo dia del mes anterior:
*////////////////////////////////////////////////////////////////*
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate() ), 0));

*////////////////////////////////////////////////////////////////*
*/ Ultimo dia del año anterior:
*////////////////////////////////////////////////////////////////*
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

*////////////////////////////////////////////////////////////////*
*/ Primer lunes del mes:
*////////////////////////////////////////////////////////////////*
SELECT DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0);


De aquí en adelante usaremos el Analizador de consultas (Query Analyzer), pero recordemos que cuando desarrollamos aplicaciones, las consultas las hacemos a través de la aplicación; al final de este artículo ampliaré un poco más sobre este tema.
/* Creamos un usuario donde su idioma predefinido es el Español */
sp_addlogin 'UserFechas','oocu','master','Español'
/* vamos a darle acceso a nuestra Base de Datos Northwind */
use Northwind
GO
sp_grantdbaccess 'UserFechas'
GO

Ahora salimos de QA (Analizador de Consultas) y volvemos a entrar con este nuevo usuario que creamos (Recordar que la autentificación de nuestro motor, en este caso, debe ser mixta y que el uso de cuentas de SQL Server no es aconsejable. Por el contrario, siempre hay que tratar de utilizar la autenticación Windows, pero para hacer el ejemplo mas fácil es que usé autenticación SQL con un usuario SQL).Lo primero que haremos es verificar que el idioma esté en español, tal 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 la consulta:

Ahora usaremos la tabla Orders y haremos la consulta de fechas de dos formas distintas: la primera usando los formatos como la mayoría está acostumbrado, y la segunda utilizando el formato ANSI, que sería lo 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'
Veremos que en ambas consultas nos trae como resultado del Count la cantidad 460, esto quiere decir que encontró 460 registros donde su orderdate es mayor o igual al 1 de Agosto de 1997. Cambiaremos el lenguaje de nuestra sesión a Inglés y hagamos la misma consulta para ver qué sucede:
use northwind
go /* Cambiamos el lenguaje para esta sesión a Inglés, 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 se 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 pasó de los 460 registros a los 670, ¿Porqué sucedió esto? Bueno, como el lenguaje de la sesión es inglés, ahora la fecha que estamos consultando es realmente mayor al 8 de enero de 1997; pero si se ejecuta la segunda consulta se verá que ésta sigue retornando los 460 registrs originales; pues bien, esto es por todo lo que hemos comentado antes. De ahí que es muy, pero muy importante, utilizar este tipo de formato (YYYYMMDD hh:mm:ss).Hemos aprendido a usar el formato ANSI para nuestras consultas (querys) 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? Esto es totalmente cierto; si ponemos por ejemplo = '20040101', esto traerá los registros no del día 01 de enero del 2004 totalmente, sino sólo aquellos que sean de las 0 horas; para solucionar esto, me gusta usar esta consulta muy simple:
Select * from orders Where orderdate >='19970805' and orderdate 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.
Observaciones
Cuando desarrollamos nuestra aplicación es muy común preguntarnos si utilizar Procedimientos Almacenados (Stored Procedures) o directamente hacer que nuestra aplicación envíe las sentencias TSQL.Es importante destacar en estos casos porqué recomiendo el uso de los Procedimientos Almacenados en lugar de que nuestra aplicación haga los TSQL y luego los pase al motor. Los Procedimientos Almacenados deben ser las reinas de los motores y de los DBA, ¿Porqué? Simple: Poner nuestras consultas en Procedimientos Almacenados nos brinda las siguientes ventajas:
Mayor optimización para el motor. Este no debe compilar cada instrucción TSQL (a menos que así lo indiquemos), sino que ya debe tenerla compilada, lo cual genera mucho mayor rendimiento. También se da el caso de que el procedimiento reside en nuestro servidor por lo que el tráfico de red es muy inferior al que produciríamos si enviáramos las TSQL desde nuestro cliente.
Seguridad. Los Procedimientos Almacenados no sólo son buenos por rendimiento, sino que nos permiten hacer las operaciones no directamente sobre nuestras tablas.Un ejemplo clásico: supongamos que tenemos una aplicación de Clientes donde sólo queremos que el listado de precios sea ejecutado por nuestras aplicaciones autorizadas y no que por medio de una planilla de Excel el usuario lo pueda hacer, en este caso si para esa consulta usamos un Procedimiento Almacenado, lo que haremos será darle permisos a nuestros usuarios al mismo y no a las tablas, por lo que para sacar la consulta debe ejecutar dicho Procedimiento.
Reutilización de código. El uso de Procedimientos Almacenados es muy útil para esto; si la consulta de rangos de fechas para buscar los pedidos en firme de un cliente, por ejemplo, la necesitamos en más de una aplicación, entonces podemos reutilizar el procedimiento sin problemas.
Reglas de negocio. En un procedimiento podemos poner reglas de negocio y hacer así que nuestras aplicaciones se beneficien de ello, aunque se debe tener cuidado con esto, pues no se debe abusar.Mi regla es que si debo usar cursores o cosas complicadas donde el desempeño esté en juego, prefiero hacerlo en un componente; de no ser ése el caso, los Procedimientos Almacenados son muy buenos y óptimos para todo esto.
Un punto a tener en cuenta respecto al uso de procedimientos (por lo menos mi experiencia así lo indica), es que si no lo hacemos en procedimiento y lo ponemos en la aplicación, luego si hay que cambiar algo seguramente debamos recompilar nuestras aplicaciones y esto es muy doloroso de verdad y difícil de mantener; y ¿Qué ocurre con los Procedimientos Almacenados? En la mayoría de los casos las modificaciones (si no tienen nuevos parámetros de entrada o salida) no afectan a la aplicación; según mi experiencia con los casos de este tipo, suelen ser casi la mayoría.
Conclusión
El uso de las fechas es un gran problema si no lo entendemos bien, y nos puede provocar múltiples dolores de cabeza; pero además de eso es muy importante que nuestras consultas puedan estar en su mayoría dentro de Procedimientos Almacenados (hay casos donde esto no es viable) y saber que TSQL no es un lenguaje de programación ni mucho menos; por lo que debemos usarlo para lo que en realidad fue creado