1. T-SQL. Funciones de Conversión CAST, CONVERT y PARSE
Se pretende ver las distintas posibilidades de conversión y su casos de uso.
CAST:
La estructura es CAST ( expression AS data_type [ (length ) ])
Sino se establece length por defecto es 30.
- Se puede usar para cálculos aritméticos
|
1 2 3 4 5 6 |
/* En la select se obtiene el total y la base de tipo numeric(9,2) y se hace un Cast a Entero*/ SELECT total, base, (total-base) as Iva, CAST(total/base AS int) as SinRedondeo, CAST(ROUND(total-base, 0) AS int) AS ConRedondeo FROM Factura WHERE total<20; |
El resultado es:
|
1 2 3 4 5 6 7 |
Total Base Iva SinRedondeo ConRedondeo 13.92 12.00 1.92 1 2 11.54 9.95 1.59 1 2 11.54 9.95 1.59 1 2 16.24 14.00 2.24 2 2 11.54 9.95 1.59 1 2 16.24 14.00 2.24 2 2 |
*Nota: se puede observar que al hacer el CAST sin el ROUND lo que hace es truncar el valor. En la tabla siguiente podéis ver los comportamientos.
Sería útil para obtener el porcentaje de descuentos aplicados ….
|
1 2 3 4 5 |
SELECT ImporteTotal,Descuento, Descuento*100/Importetotal as descuento, CAST(ROUND(Descuento*100/Importetotal,0)as int) as descuentoInt FROM lineaFactura WHERE descuento!=0 and importeTotal!=0 and importetotal>descuento; |
Resultado
|
1 2 3 4 5 6 |
ImporteTotal Descuento descuento descuentoInt 17.85 3.15 17.647058823529 18 14.00 6.00 42.857142857142 43 323.85 57.15 17.647058823529 18 377.19 3.81 1.010101010101 1 7.20 2.75 38.194444444444 38 |
- Se puede usar para concatenar.
|
1 2 3 4 5 6 7 8 |
Select 'El pedido: '+CAST(idpedido as char(4))+' fue realizado el '+ Cast(fechaPedido as nvarchar(6)) as PedidosRealizados from PedidoWeb -- Sin establecer el lenght del tipo Select 'El pedido: '+CAST(idpedido as char)+' fue realizado el '+ Cast(fechaPedido as nvarchar) as PedidosRealizados from PedidoWeb |
Resultados:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
--Primera Select PedidosRealizados El pedido: 349 fue realizado el Ene 8 El pedido: 350 fue realizado el Ene 8 El pedido: 351 fue realizado el Ene 11 El pedido: 352 fue realizado el Ene 11 El pedido: 353 fue realizado el Ene 11 --Segunda Select --El tamaño del tipo por defecto es 30 por eso deja los espacios PedidosRealizados El pedido: 9999 fue realizado el Oct 31 2013 1:50PM El pedido: 9998 fue realizado el Oct 31 2013 12:46PM El pedido: 9997 fue realizado el Oct 31 2013 12:36PM El pedido: 9996 fue realizado el Oct 31 2013 12:33PM |
- Se puede usar para obtener solo una parte del texto.
Nos interesa obtener solo los 35 primeros caracteres de error.
|
1 2 |
SELECT distinct CAST(TextoError as char(35)) as tipoerror FROM Errores |
Resultado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Tipoerror '1' is not a valid value for 'Value '12' is not a valid value for 'Valu '14' is not a valid value for 'Valu '17' is not a valid value for 'Valu '18' is not a valid value for 'Valu '2' is not a valid value for 'Value '254' is not a valid value for 'Val '5' is not a valid value for 'Value '66' is not a valid value for 'Valu A transport-level error has occurre Acceso denegado a la ruta de acceso Acceso denegado. |
- Se puede usar con la cláusula LIKE
Obtener las facturas que se han hecho a las 10 de la mañana
|
1 2 3 4 |
select idFactura, fecha from factura Where Cast(Fecha as nvarchar) like '%10:%' order by fecha desc |
Resultado:
|
1 2 3 4 5 6 7 |
idFactura fecha 41327 2014-05-12 10:27:21.037 41325 2014-05-12 10:08:29.637 41317 2014-05-09 10:52:03.460 41316 2014-05-09 10:39:59.110 41308 2014-05-08 10:06:24.867 41293 2014-05-07 10:51:29.400 |
- Se puede usar para generar instancias de XML
|
1 |
Select CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>' AS XML) as EnXml |
Resultado:
- Se puede usar con las fechas
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @d1 date, @t1 time, @dt1 datetime; SET @d1 = GETDATE(); SET @t1 = GETDATE(); SET @dt1 = GETDATE(); SET @d1 = GETDATE(); /* Utiliza la variable d1 de tipo date, al pasarla a datetime la hora es todo ceros, ya que no dispone de ella.*/ SELECT @d1 AS [date], CAST (@d1 AS datetime) AS [date as datetime]; /* utiliza la variable t1 que es de tipo time, al no disponer de la fecha pasa a ser 01/01/1900*/ SELECT @t1 AS [time], CAST (@t1 AS datetime) AS [time as datetime]; /* utiliza la variable dt1, de tipo datatime, al pasarla a date elimina la hora y al pasarla a time elimina la fecha*/ SELECT @dt1 AS [datetime], CAST (@dt1 AS date) AS [datetime as date], CAST (@dt1 AS time) AS [datetime as time]; |
Resultado:
CONVERT:
La estructura es CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Es muy interesante su uso en tipo de datos fecha y numéricos ya que permite establecer el estilo.
Ejemplo Convert de datetime en dato de caracteres
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
DECLARE @fecha AS datetime; SELECT @fecha= GETDATE(); Select @fecha as fecha --Valor predeterminado mes dd aaaa hh:mia.m. (o p. m.) Select CONVERT (CHAR,@fecha,0)as fecha0, CONVERT (CHAR,@fecha,100)as fecha100 --EE.UU. mm/dd/aa mm/dd/aaaa Select CONVERT (CHAR,@fecha,1)as fecha1, CONVERT (CHAR(10),@fecha,101)as fecha101 --EE.UU. mm-dd-aa Select CONVERT (CHAR(8),@fecha,10)as fecha10, CONVERT (CHAR(10),@fecha,110)as fecha110 --ANSI aa.mm.dd aaaa.mm.dd Select CONVERT (CHAR(8),@fecha,2) as fecha2,CONVERT (CHAR(10),@fecha,102) as fecha102 --Británico/Francés dd/mm/aa Select CONVERT (CHAR(8),@fecha,3) as fecha3,CONVERT (CHAR(10),@fecha,103) as fecha103 --Alemán dd.mm.aa Select CONVERT (CHAR(8),@fecha,4) as fecha4,CONVERT (CHAR(10),@fecha,104) as fecha104 --Italiano dd-mm-aa Select CONVERT (CHAR(8),@fecha,5) as fecha5,CONVERT (CHAR(10),@fecha,105) as fecha105 -- Japón aa/mm/dd Select CONVERT (CHAR(8),@fecha,11) as fecha11,CONVERT (CHAR(10),@fecha,111) as fecha111 --ISO aammdd Select CONVERT (CHAR(8),@fecha,12) as fecha12,CONVERT (CHAR(10),@fecha,112) as fecha112 --ODBC Canónico Select CONVERT (CHAR,@fecha,20) as fecha20,CONVERT (CHAR,@fecha,120) as fecha120 --ISO8601 y ISO8601 con zona horaria Z Select CONVERT (CHAR,@fecha,126) as fecha126, CONVERT (CHAR,@fecha,127) as fecha127 -- Otros Select CONVERT (CHAR,@fecha,106) as fecha106, CONVERT (CHAR,@fecha,107) as fecha107, CONVERT (CHAR,@fecha,108) as hora108 |
Resultado:
Convertir caracteres a datetime, y comparar con la fecha
Es lo contrario que lo anterior , lo que hay que fijarse es la fecha en texto a que formato corresponde, ya que sino se puede confundir el año con el mes , aquí tienes dos ejemplos:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT idFactura, Fecha, CONVERT(DATETIME, '2013-12-01 00:00:00', 102) as ANSI FROM Factura WHERE (Fecha > CONVERT(DATETIME, '2013-12-01 00:00:00', 102)) and (Fecha < CONVERT(DATETIME, '2013-12-03 00:00:00', 102)) SELECT idFactura, Fecha, CONVERT(DATETIME, '01.12.2013 00:00:00', 104) as Aleman FROM Factura WHERE (Fecha > CONVERT(DATETIME, '01.12.2013 00:00:00', 104)) and (Fecha < CONVERT(DATETIME, '03.12.2013 00:00:00', 104)) |
Resultado: se obtiene el mismo número de filas, ambas son correctas
El problema es si hiciéramos esta consulta
|
1 2 3 4 5 |
SELECT idFactura, Fecha, CONVERT(DATETIME, '01.12.2013 00:00:00', 102) as ANSI FROM Factura WHERE (Fecha > CONVERT(DATETIME, '01.12.2013 00:00:00', 102)) and (Fecha < CONVERT(DATETIME, '03.12.2013 00:00:00', 102)) |
La fecha la convierte a 2013-01-12 y 2013-03-12 respectivamente, obteniendo un resultado no esperado, hay que tener en cuenta el texto indicado y el formato al que convertir para no llevarnos sorpresas.
PARSE: (a partir de Sql Server 2012)
La estructura es PARSE ( string_value AS data_type [ USING culture ] )
Se usa principalmente para convertir strings a Fecha o numero.
Por ejemplo vamos a ver las conversiones usando “culture”
|
1 2 3 4 |
SELECT PARSE('12/10/2010' AS datetime2 USING 'en-US') AS ResultenUS; SELECT PARSE('12/10/2010' AS datetime2 USING 'es-ES') AS ResultesES; SELECT PARSE('12/10/2010' AS datetime2 USING 'de-DE') AS ResultdeDE; SELECT PARSE('12/10/2010' AS datetime2 USING 'ja-JP') AS ResultjaJP; |
El resultado sería el siguiente observa que es diferente según la culture
Ahora con números se puede apreciar lo mismo.
|
1 2 3 4 |
SELECT PARSE('1125,18' AS money USING 'en-US') AS ResultenUS; SELECT PARSE('1125,18' AS money USING 'es-ES') AS ResultesES; SELECT PARSE('1125,18' AS money USING 'de-DE') AS ResultdeDE; SELECT PARSE('1125,18' AS money USING 'ja-JP') AS ResultjaJP; |
A partir del SQL server 2012 y Azure SQL Database tenemos
En caso de que de error, devuelven un NULL en vez de la excepción, os pongo un ejemplo con PARSE.
|
1 2 3 4 5 |
SELECT PARSE('1.125,18' AS money USING 'en-US') AS ResultenUS; /*Devuelve: Mens. 9819, Nivel 16, Estado 1, Línea 18 Error al convertir el valor de cadena '1.125,18' en el tipo de datos money mediante la referencia cultural 'en-US'.*/ SELECT TRY_PARSE('1.125,18' AS money USING 'en-US') AS ResultenUS; --Devuelve: Null |
Espero que os sea de utilidad.