TRANSACT-SQL
T RABAJANDO
CON FECHAS
SQL Server provee tipos de datos especiales para el manejo de valores fechas y hora, estos tipos facilitan el
manejo y validacin de este tipo de informacin tan importante.
Hasta SQL 2008 los tipos de datos para guardar valores tipo fecha haban sido los tipos de datos DATETIME y
SMALLDATETIME, estos tipos de datos almacenan tanto la hora como la fecha de forma conjunta. Luego en
SQL 2008 aparecen otros tipos que agregan muchas posibilidades como son: manejar la fecha y la hora de
forma separada, rango de validez incrementado, etc.
ALMACENAMIENTO
En qu formato se guarda el valor?, SQL guarda los datos de fecha y hora como valores enteros de 2 o 4 bytes.
El tipo DATETIME toma los primeros 4 bytes para la fecha y los otros 4 bytes para la hora, el tipo
SMALLDATETIME toma 2 bytes para cada valor. Esto quiere decir que la fecha no se guarda en DD/MM/YYY o
MM/DD/YYYY, etc., sino como un nmero.
TIPOS DE DATOS
TIPO
DATETIME
SMALLDATETIME
TIMESTAMP
Bytes
8
4
Rango
Jan 1, 1753 - Dec 31, 9999
Jan 1, 1900 - Jun 6, 2079
0001-01-01 - 9999-12-31
3a5
00:00:00.0000000 - 23:59:59:9999999
3a6
0001-01-01
9999-12-31
(Se utiliza para marcar un registro con la fecha de
actualizacin o insercin)
DATE
(SQL 2008)
TIME
(Nuevo en SQL
hh:mm:ss.nnnnnnn)
2008.
Maneja
el
formato
DATETIME2
(Es similar al DATETIME pero con un mayor rango.)
00:00:00.0000000
23:59:59.9999999
INSERTANDO
SQL soporta varios formatos para insertar o colocar un valor tipo fecha, estos valores sea cual sea el formato
deben ser fechas validas. La mayor parte de los formatos validar las configuraciones del lenguaje donde se
est aplicando la sentencia, sin embargo resulta mucho ms prctico utilizar el estndar ANSI (YYYYMMDD
HH:MM:SS) y no depender de configuraciones regionales.
Antes de existir el tipo de dato DATE debamos tener especial cuidado al momento de insertar en valor tipo
fecha con el tema de la parte del tiempo (HH:MM:SS). Si el inters es manejar una fecha corta (YYYYMMDD)
entonces se registra 00:00:00 en la parte del tiempo, esto permite que la fecha puede ser consultada sin indicar
el tiempo. Si el inters es manejar la fecha larga (YYYYMMDD HH:MM:SS) entonces debemos siempre
consultar la fecha tomando en cuenta el tiempo, de lo contrario esos registros que tienen ese dato del tiempo no
apareceran a menos que se filtre por un rango: Fecha BETWEEN 2011-01-01 00:00:00 and 2011-01-01
SQL Server Carlos Caraballo
Page 1
23:59:00, algo as tomara en cuenta todos los registros de la fecha 2011-01-01 con cualquier hora que tenga
registrada.
INSERT INTO empleado( fecha) VALUES( 20110120 22:01:20)
Insertando la fecha en formato ANSI. La hora se indica en el esquema de 24 horas.
INSERT INTO empleado( fecha) VALUES( 20110120)
Insertando la fecha en formato ANSI. Si el campo fecha es un DATETIME se grabar 00:00:00 en la parte del tiempo,
esto permite que el registro pueda ser consultado indicando solo la parte de la fecha. Cuando se indica la hora siempre
tendremos que contemplar la hora en la consulta.
SET DATEFORMAT
Si no se quiere utilizar el formato ANSI el parmetro DATEFORMAT puede ser utilizado para indicar el formato
(da/mes o mes/da) que deseamos utilizar para registrar o consultar un valor tipo fecha.
SET DATEFORMAT mdy
INSERT INTO empleado( fecha) VALUES( 01/20/2010)
Se le indica al manejador aceptar el valor en el formato mes da.
SET DATEFORMAT dmy
INSERT INTO empleado( fecha) VALUES( 20/01/2010)
Se le indica al manejador aceptar el valor en el formato da mes.
FORMATOS Y CONVERSIONES
Las funciones CAST y CONVERT pueden ser utilizadas para convertir valores fechas, de string a fecha y
viceversa o convertir una fecha a un formato o estilo indicado. Ambas funciones trabajan las labores de
conversin, sin embargo solo la funcin CONVERT nos permite indicar el formato de fecha para el resultado. A
continuacin vamos a ver una tabla con los diferentes formatos que podemos aplicar.
Without century (yy)
With century (yyyy)
Standard
Input/Output**
0 or 100 (*)
Default
mon dd yyyy hh:miAM (or PM)
101
USA
mm/dd/yy
102
ANSI
yy.mm.dd
103
British/French
dd/mm/yy
104
German
dd.mm.yy
SQL Server Carlos Caraballo
Page 2
105
Italian
dd-mm-yy
106
dd mon yy
107
Mon dd, yy
108
hh:mm:ss
9 or 109 (*)
Default + milliseconds
mon dd yyyy
hh:mi:ss:mmmAM (or PM)
10
110
USA
mm-dd-yy
11
111
JAPAN
yy/mm/dd
12
112
ISO
yymmdd
13 or 113 (*)
Europe default +
dd mon yyyy
milliseconds
hh:mm:ss:mmm(24h)
14
114
hh:mi:ss:mmm(24h)
20 or 120 (*)
ODBC canonical
yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (*)
ODBC canonical (with
yyyy-mm-dd
milliseconds)
hh:mi:ss.mmm(24h)
ISO8601
yyyy-mm-dd
126(***)
Thh:mm:ss.mmm(no spaces)
-
130*
Hijri****
dd mon yyyy
hh:mi:ss:mmmAM
131*
Hijri****
dd/mm/yy hh:mi:ss:mmmAM
http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx
FORMATOS EN LA FECHA
Format #
Query (current date: 12/30/2006)
Sample
select convert(varchar, getdate(), 1)
12/30/06
select convert(varchar, getdate(), 2)
06.12.30
select convert(varchar, getdate(), 3)
30/12/06
select convert(varchar, getdate(), 4)
30.12.06
select convert(varchar, getdate(), 5)
30-12-06
SQL Server Carlos Caraballo
Page 3
select convert(varchar, getdate(), 6)
30 Dec 06
select convert(varchar, getdate(), 7)
Dec 30, 06
10
select convert(varchar, getdate(), 10)
12-30-06
11
select convert(varchar, getdate(), 11)
06/12/30
101
select convert(varchar, getdate(), 101)
12/30/2006
102
select convert(varchar, getdate(), 102)
2006.12.30
103
select convert(varchar, getdate(), 103)
30/12/2006
104
select convert(varchar, getdate(), 104)
30.12.2006
105
select convert(varchar, getdate(), 105)
30-12-2006
106
select convert(varchar, getdate(), 106)
30 Dec 2006
107
select convert(varchar, getdate(), 107)
Dec 30, 2006
110
select convert(varchar, getdate(), 110)
12-30-2006
111
select convert(varchar, getdate(), 111)
2006/12/30
FORMATOS PARA TIEMPO
8 or 108
select convert(varchar, getdate(), 8)
00:38:54
9 or 109
select convert(varchar, getdate(), 9)
Dec 30 2006 12:38:54:840AM
14 or 114
select convert(varchar, getdate(), 14)
00:38:54:840
SELECT CAST ( fecha as varchar )..
Convierte una fecha en un valor alfanumrico
SET DATEFORMAT dmy
SELECT CAST( 20/01/2011 as DATETIME)
Convierte un valor alfanumrico en una valor tipo fecha, en este caso se utiliza DATEFORMAT para indicar el formato
que estamos utilizando para indicar el valor a convertir.
SET LENGUAGE us_english
SELECT CAST( 01/20/2011 as DATETIME)
Se utiliza SET LENGUAGE para indicar la cultura del valor a convertir.
SELECT CAST( CONVERT( VARCHAR,GETDATE(),101) AS DATETIME )
Extrae la fecha corta de la fecha larga actual y convierte dicho valor en un valor tipo fecha.
F U N CI O N E S
SQL provee un grupo de funciones que permiten desde conseguir la fecha y hora actual del sistema hasta
realizar cualquier tipo de operaciones matemticas con estos valores.
Funcin
Descripcin
SQL Server Carlos Caraballo
Ejemplo
Page 4
CURRENT_TIMESTAMP
GETDATE()
DAY
MONTH
YEAR
DATEPART
Retorna la fecha completa actual. Con
este valor se retorna la fecha completa
para cada fila retornada.
Es
un
equivalente
de
CURRENT_TIMESTAMP
con
la
diferencia de que ste puede cambiar a
lo largo del resultado.
Se utiliza para extraer el da de la fecha
Extrae el mes de la fecha
Extrae el ao de la fecha
Se utiliza para extraer cualquier parte
de la fecha.
hh: hora
yy: ao
dd: da de la semana
wk: semana
mi: minuto
ss: segundo
dy: da del ao
dw: da de la semana
qq: Quarter
SELECT CURRENT_TIMESTAMP
Res: 2011-01-01 12:20:20
SELECT GETDATE()
SELECT DAY(GETDATE())
SELECT MONTH( GETDATE())
SELECT YEAR( GETDATE())
Fecha: '5 December 2009 13:24:56'
DATEPART( hh,fecha) : 13
DATEPART( dy,fecha) : 339
DATEPART( dw,fecha) : 7
DATEPART( yy,fecha) : 2009
El formato tambin puede indicarse en
palabra completa, por ejemplo en vez
de utilizar yy podemos decir year.
http://msdn.microsoft.com/en-us/library/ms189794.aspx
DATENAME
DATEDIFF
DATEADD
Extrae el literal de la parte extrada de
la fecha. Es decir si sacamos el mes, nos
indica el nombre del mes.
Se utiliza para restar valores fechas.
Igual que con DATEPART es importante
se debe indicar el formato en que se
quiere el resultado.
Se utiliza para sumar un valor a una
fecha. El valor a sumar se indica de
acuerdo a las constantes que se utilizan
para extraer una parte de la fecha.
Fecha: 12/20/2010
SELECT DATENAME(mm,fecha)
Saca el valor Diciembre
SELECT DATEDIFF( dd,fecha,GETDATE() )
Resta GETDATE fecha y devuelve el
resultado en das.
Fecha: 12/20/2010
SELECT DATEADD(mm,2,fecha)
Suma 2 meses al valor fecha.
R EFERENCIAS
http://msdn.microsoft.com/en-us/library/aa226054(v=sql.80).aspx
http://www.blackwasp.co.uk/SQLDateTimeInfoFunctions.aspx
http://msdn.microsoft.com/en-us/library/ms189794.aspx
SQL Server Carlos Caraballo
Page 5
