SQL Server 2008
Nuevos tipos de datos de
fecha y hora
Información sobre versión Beta
• Tanto la información como los ejemplos son
relativos a la beta pública de SQL Server 2008
CTP5
• Tanto comandos como funcionalidades pueden
diferir de la versión RTM final
2
Tipos “Date” y “Time”
• Separación de tipos de fecha y hora
• Mayor precisión de fracciones de segundo
• Mayor rango de datos
• Conocimiento de zona horaria
• Consecuente y/o equivalente al SQL Estándar
• Migración de Base de Datos
– Compatibilidad con soluciones de la competencia Escenarios y requerimientos
Tipos “Date” y “Time”
• DATE
– Solo fechas
– Gran rango desde 0001-01-01 al 9999-12-31 – Tipo estándar de SQL
– Tamaño fijo de almacenamiento de 3 bytes Tipo de datos “Date”
Tipos “Date” y “Time”
• Numeric
– mm/dd/yyyy, dd-mm-yyyy, mm.dd.yyyy
• Alphabetical
– mon dd, yyyy• ISO 8601
– yyyy-mm-dd, yyyymmdd• ODBC
– {„yyyy-mm-dd‟}• W3C XML
– yyyy-mm-ddZTipos “Date” y “Time”
• TIME(n)
– Solo tiempo
– Precisión opcional asignable por el usuario • Hasta 100 nanosegundos (por defecto)
• Por defecto alineado con el SO y .NET – Formato
• HH:MI:SS[.nnnnnnn] • Se soportan otros
– Tipo de datos SQL Estándar
– Almacenamiento variable de 3 a 5 bytes Tipo de datos TIME
Tipos “Date” y “Time”
• DATETIMEOFFSET(n)
– Contiene fecha, hora y offset para zona horaria
– Gran rango de datos desde 0001-01-01 a 9999-12-31 – Precisión opcionalmente especificable por el usuario
• Hasta 100 nanosegundos (por defecto) • Por defecto alineado con el SO y .NET – Formato
• YYYY-MM-DD HH:MI:SS.[.nnnnnnn][+|-]HH:MI
• Ófsets de zona horaria con rangos desde -14:00 a 14:00 – Tipo equivalente a SQL Estándar
• TIMESTAMP WITH TIME ZONE
– Tamaño de almacenamiento variable de 8 a 10 bytes Tipo de datos con zona horaria
Tipos “Date” y “Time”
• Los datos son almacenados como valores UTC
• Se realiza procesamiento como valores UTC
– comparaciones – ordenación
– indexación
• Se puede devolver la zona horaria original
Tipos “Date” y “Time”
• DATETIME2(n)
– Gran rango de datos desde 0001-01-01 a 9999-12-31 – Precisión opcionalmente especificable por el usuario
• Hasta 100 nanosegundos (por defecto) • Por defecto alineado con el SO y .NET
– Equivalente al Estándar SQL (TIMESTAMP)
– Tamaño de almacenamiento variable de 6 a 8 bytes – No soporta zona horaria
– Alineado con el tipo nativo de .NET DateTime
– Reemplazo recomendado de los actuales DateTime y smalldatetime
Tipos “Date” y “Time”
• datetime2 soporta los formatos ISO 8601
completamente
– yyyy-mm-dd – yyyymmdd
• Los “antiguos” DateTime *todavía* no gestionan
yyyy-mm-dd como un formato ISO
– Ten cuidado!
– Realizado para evitar cambios de comportamiento inesperados
Nuevos tipos date/time mejorados
10
Tipos “Date” y “Time”
• Por defecto: 100 nanosegundos
– 10-millonésima de segundo
– Alineado con Windows y con DateTime de .NET
• Parámetros de tipo opcional
– Numero de dígitos para la parte fraccional de segundo
– Puede ser un entero de 0 a 7 (100 nanosegundos) Fracciones de segundo
Tipo de columna Formato de cadena literal por defecto Precision Escala
DATE YYYY-MM-DD 10 0
TIME HH:MI:SS [.nnnnnnn] 16 7
DATETIMEOFFSET YYYY-MM-DD HH:MI:SS[.nnnnnnn][+|-]HH:MI 34 7 DATETIME2 YYYY-MM-DD HH:MI:SS[.nnnnnnn] 27 7
Tipos “Date” y “Time”
• Tipo de datos nativo, completamente integrado
– Compatible con todas las funciones (built-in) – Todas las funcionalidades
• Nuevas partes de fecha añadidas
– DATEADD, DATEDIFF, DATEPART, DATENAME – microsegundos (mcs)
– nanosegundos (ns) – TZoffset (tz)
– Número de semana ISO 8601 Integración en servidor
Tipos “Date” y “Time”
• Valores del sistema
– SYSDATETIME(), SYSUTCDATETIME() – SYSDATETIMEOFFSET()
• Consciencia del horario de verano según el SO
• Manipulación del offset de zona horaria
– TODATETIMEOFFSET() – SWITCHOFFSET()
Tipos “Date” y “Time”
• Los metadatos son nvarchar o varchar
• Los datos son una cadena de caracteres literal
en formato SQL/ISO
Soporte a bajo nivel en aplicaciones cliente
Tipo de columna Formato por defecto de cadena literal
DATE YYYY-MM-DD
TIME HH:MI:SS [.nnnnnnn]
DATETIMEOFFSET YYYY-MM-DD HH:MI:SS[.nnnnnnn][+|-]HH:MI DATETIME2 YYYY-MM-DD HH:MI:SS[.nnnnnnn]
Tipos “Date” y “Time”
• Soporte completo para xs:dateTime
– Soporte para valores sin zona horaria
– Se puede preservaar el offset de zona horaria
• Soporte para .NET nullable<T> en todos los
tipos nativos
– Ya no hacen falta SqlTypes
– Modelo de programación consistente con el CLR Funcionalidades avanzadas del servidor
Tipos “Date” y “Time”
• Obtener el offset temporal del sistema
• Creación de un tipo de datos atómico: “birthday”
Casos de uso típicos en T-SQL(1)
SELECT DATENAME(tz, SYSDATETIMEOFFSET()) AS TZ;
TZ
---+02:00
CREATE TYPE birthday FROM DATE NOT NULL;
GO
CREATE TABLE Contact (
ContactID int NOT NULL PRIMARY KEY
, ContactName varchar(100) NOT NULL
, ContactBirthday birthday NOT NULL
Tipos “Date” y “Time”
• DATEFORMAT() no afecta a los literales ISO
• Obtener la fecha y hora actuales del sistema
Casos de uso típicos en T-SQL(2)
CREATE TABLE T1 (f1 datetime NOT NULL PRIMARY KEY)
CREATE TABLE T2 (f1 datetime2 NOT NULL PRIMARY KEY)
SET DATEFORMAT ydm
INSERT T1 VALUES ('2007-08-13') -- esto falla!
INSERT T2 VALUES ('2007-08-13')
SELECT SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME()
DT Function Result
--- ---SYSDATETIME() 2007-10-05 00:23:24.1904448 +00:00 SYSDATETIMEOFFSET() 2007-10-05 00:23:24.1904448 +02:00
Tipos “Date” y “Time”
• Obtener fecha/hora local o UTC datetimeoffset
• Obtener fecha/hora local de una zona horaria
distinta
Casos de uso típicos en T-SQL(3)
SELECT SYSDATETIME(), SYSDATETIMEOFFSET() , CONVERT(datetime2 , SWITCHOFFSET(SYSDATETIMEOFFSET(), '+01:00')); DT Function Result --- ---SYSDATETIME() 2007-10-05 01:20:38.6990256 +00:00 SYSDATETIMEOFFSET() 2007-10-05 01:20:38.6990256 +02:00 SWITCHOFFSET(...) 2007-10-05 00:20:38.6990256 +00:00
SELECT CONVERT(datetime2, SYSDATETIMEOFFSET(), 0) , CONVERT(datetime2, SYSDATETIMEOFFSET(), 1);
TZ Result
--- ---Local 2007-10-05 00:57:27.7188944 UTC 2007-10-04 22:57:27.7188944
Tipos “Date” y “Time”
• Utilizando CAST/CONVERT con index seek
Casos de uso típicos T-SQL(4)
CREATE TABLE dbo.Search (MyDate datetime2 NOT NULL PRIMARY KEY);
SELECT MyDate
FROM dbo.Search
WHERE MyDate = '09:00:00';
SELECT MyDate
FROM dbo.Search
WHERE CONVERT(time(0), MyDate) = '09:00:00';
SELECT MyDate
FROM dbo.Search
Tipos “Date” y “Time”
• Completo soporte SNAC (ODBC/OLEDB) en
Katmai
• Completo soporte SqlClient/ADO.NET en Orcas
Soporte en proveedores cliente
SQL ODBC OLEDB ADO.NET
DATE SQL_TYPE_DATE, SQL_DATE DBTYPE_DBDATE DateTime TIME SQL_TIME, SQL_SS_TIME2 DBTYPE_DBTIME, DBTYPE_DBTIME2 TimeSpan DATETIMEOFFSET SQL_SS_TIMESTAMPO FFSET DBTYPE_DBTIMESTAMP OFFSET DateTimeOffset DATETIME2 SQL_TYPE_TIMESTAMP , SQL_TIMESTAMP DBTYPE_DBTIMESTAMP DateTime
Tipos “Date” y “Time”
• Nuevos tópicos específicos en CTP4 y
posteriores
– Tipos de datos fecha, hora, y funciones (T-SQL) – Usando datos fecha y hora
– Mapeo de tipos de datos temporales con consultas distribuidas
– Utilizando XML con los nuevos tipos de datos de fecha y hora
DEMO 1
Fecha y hora
22
