Sql Server 2012 tiene varios tipos de datos para trabajar
con fechas y horas.
Cada uno de estos tipos tiene diferentes características y
comportamientos que debemos conocer para evitar errores en nuestras
aplicaciones.
El tipo datetime
es un tipo que existe desde las primeras versiones de Sql Server, el rango de
valores esta limitado a fechas entre 1
enero de 1753 y 31 de diciembre de
9999, la precisión es de 3 milisegundos y siempre redondea a 0, 3 ó 7
milisegundos.
Aunque parece que este tipo de dato sea bastante preciso, el
redondeo puede producir errores.
Veamos un ejemplo:
declare @FechaHora datetime;
set @FechaHora = '20120402 12:00:00';
select @FechaHora -- Muestra 2012-04-02 12:00:00.000 OK
set @FechaHora = '20120402 12:00:00.000';
select @FechaHora -- Muestra 2012-04-02 12:00:00.000 OK
set @FechaHora = '20120402 12:00:00.200';
select @FechaHora -- Muestra 2012-04-02 12:00:00.200 OK
set @FechaHora = '20120402 12:00:00.220';
select @FechaHora -- Muestra 2012-04-02 12:00:00.220 OK
set @FechaHora = '20120402 12:00:00.223';
select @FechaHora -- Muestra 2012-04-02 12:00:00.223 OK
set @FechaHora = '20120402 12:00:00.225';
select @FechaHora -- Muestra 2012-04-02 12:00:00.227 KO
set @FechaHora = '20120402 12:00:59.999';
select @FechaHora -- Muestra 2012-04-02 12:01:00.000 KO
set @FechaHora
= '20120402 23:59:59.999';
select @FechaHora
-- Muestra 2012-04-03 00:00:00.000
Otro problema con este tipo de dato es la conversión cadenas
de texto y fechas, un valor con el formato YYYYMMDD funciona siempre de forma
correcta, pero un valor con formato YYYY-MM-DD se interpretara en función de
las opciones de la conexión.
Para evitar este
problema podemos usar SET LANGUAGE o
SET DateFormat, la primera acepta el
nombre del idioma y establece el orden de las partes de la fecha tal y como
estén definidas en la tabla sys.syslanguages, la segunda aceptar un texto con
el orden de las partes de la fecha, como por ejemplo dmy, mdy, etc.
Desde la versión 2012 también
podemos usar la función DATETIMEFROMPARTS,
pasando las partes de la fecha como argumentos.
El siguiente ejemplo
plantea el uso de estas instrucciones:
declare @FechaHora datetime;
set @FechaHora = '20120402';
select @FechaHora
-- Muestra mes 04
set @FechaHora
= '2012-04-02';
select @FechaHora
-- Muestra mes 02 depende de sesión
SET LANGUAGE US_ENGLISH;
set @FechaHora = '2012-04-02';
select @FechaHora -- Muestra mes 04
SET LANGUAGE Spanish;
set @FechaHora = '2012-04-02';
select @FechaHora -- Muestra mes 02
set @FechaHora = '02/04/2012';
select @FechaHora -- Muestra mes 04
SET DateFormat ymd ;
set @FechaHora = '2012-04-02';
select @FechaHora -- Muestra mes 04
SET DateFormat dmy ;
set @FechaHora
= '02/04/02';
select @FechaHora
-- Muestra mes 04
set @FechaHora = DATETIMEFROMPARTS(2012, 04, 02, 12, 45, 36, 0);
select @FechaHora -- Muestra mes 04
Otro tipo existente en
Sql Server es smalldatetime permite
almacenar fechas en un rango que va desde 1
de enero de 1900 al 6 de junio de
2079 sin segundos, si al asignar un valor el segundo está por debajo de 29.998
se redondea al minuto inferior, si está por encima se redondea al segundo
superior.
También desde la versión
2012 también podemos usar la función SMALLDATETIMEFROMPARTS,
pasando las partes de la fecha como argumentos.
El siguiente código
muestra los posibles redondeos:
declare @FechaHora smalldatetime;
set @FechaHora = '20120402 12:00:00';
select @FechaHora -- Muestra 2012-04-02 12:00:00 OK
set @FechaHora = '20120402 12:00:00.000';
select @FechaHora -- Muestra 2012-04-02 12:00:00 OK
set @FechaHora = '20120402 12:00:00.900';
select @FechaHora -- Muestra 2012-04-02 12:00:00 KO
set @FechaHora = '20120402 12:00:29.998';
select @FechaHora -- Muestra 2012-04-02 12:00:00 KO
set @FechaHora = '20120402 12:00:29.999';
select @FechaHora -- Muestra 2012-04-02 12:01:00 KO
set @FechaHora = '20120402 12:00:45';
select @FechaHora
-- Muestra 2012-04-02 12:01:00 KO
set @FechaHora = SMALLDATETIMEFROMPARTS(2012, 04, 02, 12, 01 );
select @FechaHora -- Muestra 2012-04-02 12:01:00 OK
Uno de los nuevos
(disponible desde 2008) tipos de fecha de Sql Server es el tipo date que almacena una fecha con un
rango de valores que va desde 1 de enero
de 0001 al 31 de diciembre del 9999
con una precisión de un día, este tipo de dato cumple con la definición de
calendario gregoriano.
Con este tipo no tenemos
los mismos problemas de conversión entre cadenas y fechas, las cadenas YYYYMMDD
y YYYY-MM-DD se evalúan de forma correcta.
Con el tipo date podemos usar la función DATEFROMPARTS que esta disponible desde
la versión 2012 de Sql Server.
declare @Fecha date;
set @Fecha = '20120402 12:00:00';
select @Fecha
-- Muestra mes 04
set @Fecha
= '20120402';
select @Fecha
-- Muestra mes
04
set @Fecha = '2012-04-02';
select @Fecha -- Muestra mes 04
set @Fecha = '2012/04/02';
select @Fecha
-- Muestra mes
04
set @Fecha
= '02/04/2012';
select @Fecha
-- Muestra mes
04
set @Fecha = DATEFROMPARTS(2012, 04, 02 );
select @Fecha -- Muestra mes 04
Otro de los nuevos tipos
de datos es el tipo time que almacena
la hora desde las 00:00:00 a las 23:59:59, permite especificar el numero
de decimales que almacena para los segundos, las fracciones de segundo se
definen de 0 a 7.
Para dar valor a el tipo
time podemos usar la función TIMEFROMPARTS que acepta como
argumentos las diferentes partes de una hora mas un ultimo argumento que indica
la precisión de tipo time devuelto.
declare @Hora Time; --precision 7
set @Hora = '12:00:00';
select @Hora -- Muestra 12:00:00.0000000
set @Hora = '12:00:00.9999999';
select @Hora -- Muestra 12:00:00.9999999
set @Hora = '12:00:00.0000001';
select @Hora -- Muestra 12:00:00.0000001
set @Hora = '12:00:00.01';
select @Hora -- Muestra 12:00:00.0100000
set @Hora = '12:00:00.99';
select @Hora -- Muestra 12:00:00.9900000
declare @Hora1 Time(2);
set @Hora1 = '12:00:00';
select @Hora1 -- Muestra 12:00:01.00
set @Hora1 = '12:00:00.9999999';
select @Hora1
-- Muestra 12:00:01.00 redondeo
set @Hora1
= '12:00:00.99';
select @Hora1 -- Muestra 12:00:00.01 12:00:00.99
set @Hora1 = '12:00:00.01';
select @Hora1 -- Muestra 12:00:00.01
set @Hora1 = '12:00:00.0000001';
select @Hora1 -- Muestra 12:00:00.00 redondeo
declare @Hora2 Time(0);
set @Hora2 = '12:00:00';
select @Hora2 -- Muestra 12:00:00
set @Hora2 = '12:00:00.9999999';
select @Hora2
-- Muestra 12:00:01 redondeo
set @Hora2
= '12:00:00.01';
select @Hora2
-- Muestra 12:00:00 redondeo
set @Hora2
= '12:00:34';
select @Hora2
-- Muestra 12:00:34
set @Hora2 = TIMEFROMPARTS(12, 0, 34, 0, 0 );
select @Hora2 -- Muestra 12:00:34
También tenemos un datetime2 que combina la funcionalidad
del tipo date y el tipo time, permitiendo definir la precisión
de las fracciones de segundo.
Con el tipo datetime2
podemos usar la función DATETIME2FROMPARTS
que acepta los componentes de la fecha y la hora.
declare @FechaHora datetime2;
set @FechaHora = '20120402 12:00:00';
select @FechaHora -- Muestra 2012-04-02 12:00:00.0000000
set @FechaHora = '20120402 12:00:00.000';
select @FechaHora -- Muestra 2012-04-02 12:00:00.0000000
set @FechaHora = '20120402 12:00:00.0000001';
select @FechaHora -- Muestra 2012-04-02 12:00:00.0000001
declare @FechaHora1 datetime2(0);
set @FechaHora1 = '20120402 12:00:29';
select @FechaHora -- Muestra 2012-04-02 12:00:29
set @FechaHora1 = '20120402 12:00:29.999';
select @FechaHora1
-- Muestra 2012-04-02 12:00:30 redondeo
El último tipo de dato
de tipo fecha es datetimeoffset que
combina la funcionalidad de datetime2
con una zona horaria que varía desde -14:00
a 14:00, al igual que datetime2
permite especificar la precisión de las fracciones de segundo.
Con DATETIMEOFFSETFROMPARTS podemos dar valor al tipo datetimeoffset.
declare @FechaHora datetimeoffset ;
set @FechaHora = '20120402 12:00:00';
select @FechaHora -- Muestra 2012-04-02 12:00:00.0000000 +00:00
set @FechaHora = '20120402 12:00:00 +1:00';
select @FechaHora -- Muestra 2012-04-02 12:00:00.0000000 +01:00
set @FechaHora = '20120402 12:00:00.0000001
-10:30';
select @FechaHora -- Muestra 2012-04-02 12:00:00.0000001 -10:30
declare @FechaHora1 datetimeoffset(0);
set @FechaHora1 = '20120402 12:00:29';
select @FechaHora1 -- Muestra 2012-04-02 12:00:29 +00:00
set @FechaHora1
= '20120402 12:00:29Z'; -- Hora UTC
select @FechaHora1
-- Muestra 2012-04-02 12:00:29 +00:00
Para pasar de unos tipos
a otros podemos usar las funciones CAST,
CONVERT y PARSE.
Con Cast y Convert
convertimos entre los diferentes tipos de datos, Convert acepta un parámetro que especifica el formato de cultura.
Parse nos permite pasar de cadena de texto al tipo de dato usando
una referencia cultural.
declare @FechaHora datetimeoffset ;
set @FechaHora = '20120402 12:00:00 +1:00';
select @FechaHora, -- 2012-04-02
12:00:00.0000000 +01:00
, CAST(@FechaHora as varchar(37)) -- 2012-04-02
12:00:00.0000000 +01:00
, CONVERT(varchar(15), @FechaHora, 110) -- 04-02-2012
, CONVERT(varchar(15), @FechaHora, 103) -- 02/04/2012
, CONVERT(varchar(25), @FechaHora, 127) -- 2012-04-02T11:00:00Z
declare @FechaHora1 datetime2;
set @FechaHora1 = PARSE('31 December 2010' AS datetime2 USING 'en-US');
select @FechaHora1
-- 2010-12-31 00:00:00.0000000
Por último, para decidir
que tipo usar, aparte de conocer sus valores y comportamientos necesitamos
conocer el espacio que ocupan:
- Datetime ocupa siempre 8 bytes.
- Smalldatetime ocupa siempre 4 bytes.
- Date ocupa siempre 3 bytes.
- Time ocupa de 3 a 5 bytes en función de la precisión.
- Datetime2 ocupa de 6 a 8 bytes en función de la precisión.
- Datetimeoffset ocupa de 8 a 10 bytes en función de la precisión.
De forma general
deberíamos elegir el tipo de dato que cumple con nuestros requerimientos y
ocupa el menor espacio en memoria.