{"id":175,"date":"2017-05-10T20:41:59","date_gmt":"2017-05-10T20:41:59","guid":{"rendered":"http:\/\/www.manualsqlserver.com\/?p=175"},"modified":"2020-05-21T00:33:19","modified_gmt":"2020-05-21T00:33:19","slug":"funciones-de-conversion-de-datos","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=175","title":{"rendered":"Funciones de Conversi\u00f3n de datos"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"307\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ConversionDatosD.png\" alt=\"\" class=\"wp-image-1419\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ConversionDatosD.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/05\/ManualSQL_ConversionDatosD-293x300.png 293w\" sizes=\"auto, (max-width: 300px) 100vw, 300px\" \/><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\"><strong>Funciones para conversi\u00f3n de datos<\/strong><\/h1>\n\n\n\n<p>Los datos en muchas ocasiones deben ser convertidos y transformados en un est\u00e1ndar para poder tratarlos, para esto se deben usar las funciones de Conversi\u00f3n de datos.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p><strong>Las siguientes funciones admiten la conversi\u00f3n de tipos de datos.<\/strong><\/p>\n\n\n\n<p><strong>CAST y CONVERT<\/strong> &#8211; Convierte una expresi\u00f3n de un tipo de datos en otro.<br><strong>PARSE<\/strong> &#8211; Devuelve el resultado de una expresi\u00f3n, traducido al tipo de datos solicitado en SQL Server.<br><strong>TRY_CONVERT<\/strong> &#8211; Devuelve una conversi\u00f3n de valor al tipo de datos especificado si la conversi\u00f3n se realiza correctamente; de lo contrario, devuelve NULL.<br><strong>TRY_PARSE<\/strong> &#8211; Devuelve el resultado de una expresi\u00f3n, traducido al tipo de datos solicitado, o NULL si se produce un error en la conversi\u00f3n en SQL Server. Use TRY_PARSE solo para convertir de tipos de cadena a tipos de fecha y hora y de n\u00famero.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>Sintaxis y Ejemplos<\/strong><\/h3>\n\n\n\n<p><strong>Sintaxis CAST<\/strong><br>CAST ( expression AS data_type [ ( length ) ] )<\/p>\n\n\n\n<p><strong>Sintaxis CONVERT<\/strong><br>CONVERT ( data_type [ ( length ) ] , expression [ , style ] )<\/p>\n\n\n\n<p><strong>Donde:<\/strong><\/p>\n\n\n\n<p><strong>expresi\u00f3n:<\/strong> Se trata de cualquier expresi\u00f3n.<br><strong>data_type:<\/strong> Es el tipo de datos de destino. Esto incluye xml, bigint, y sql_variant. No se pueden utilizar tipos de datos de alias.<br><strong>longitud:<\/strong> Es un n\u00famero entero opcional que especifica la longitud del tipo de datos de destino. El valor predeterminado es 30.<br><strong>estilo:<\/strong> Expresi\u00f3n entera que especifica c\u00f3mo la funci\u00f3n CONVERT convertir expresi\u00f3n. Si style es NULL, se devuelve NULL. Determina el intervalo por data_type.<\/p>\n\n\n\n<p>Para la conversi\u00f3n de datos se debe tener en cuenta si el tipo de dato original es posible de convertir al valor resultante.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Ejercicios<\/h3>\n\n\n\n<p><strong>&#8212; Uso de CAST<\/strong><br>&#8211;<strong>&#8211; Listar los productos cuyo precio inicia con el d\u00edgito 3<\/strong><br>USE Northwind<br>go<br>Select SUBSTRING(ProductName, 1, 30) As Producto, UnitPrice As Precio<br>FROM Products Where CAST(UnitPrice As int) LIKE &#8216;3%&#8217;<br>go<br>&#8212; Note que Cast convierte el Precio (UnitPrice) a Entero (int)<\/p>\n\n\n\n<p><strong>&#8212; Use CONVERT. <\/strong><br>USE Northwind<br>GO<br>Select SUBSTRING(ProductName, 1, 30) AS Producto, UnitPrice<br>FROM Products Where CONVERT(int, UnitPrice) LIKE &#8216;3%&#8217;<br>GO<\/p>\n\n\n\n<p><strong>&#8212; Listado con el precio del producto concatenado.<\/strong><br>Use Northwind<br>go<br>Select P.ProductName + &#8216;tiene precio de lista en: &#8216; + CAST(P.UnitPrice AS varchar(12)) AS Lista<br>FROM Products As P Where UnitPrice BETWEEN 35.00 AND 40.00<br>go<\/p>\n\n\n\n<p><strong>Convirtiendo fechas<\/strong><br>Select GETDATE() AS Original, CAST(GETDATE() AS nvarchar(30)) AS &#8216;Convertida con Cast&#8217;,<br>CONVERT(nvarchar(30), GETDATE(), 126) AS &#8216;Convertida con Convert ISO8601&#8217;<br>go<\/p>\n\n\n\n<p><strong>Parse<\/strong><br>PARSE ( ValorString AS TipoDato [ USING culture ] )<\/p>\n\n\n\n<p><strong>Interpretar el DateTime2<\/strong><br>SELECT PARSE(&#8216;Saturday, 24 February 1996&#8217; AS datetime2 USING &#8216;en-US&#8217;) AS Resultado<br>go<\/p>\n\n\n\n<p><strong>Interpretar datos num\u00e9ricos<\/strong><br>SELECT PARSE(&#8216;\u20ac345.98&#8217; AS money USING &#8216;de-DE&#8217;) AS &#8216;Moneda Alemania&#8217;<br>go<\/p>\n\n\n\n<p><strong>Try_Convert<\/strong><br><strong>TRY_CONVERT ( data_type [ ( length ) ], expression [, style ]<\/strong><\/p>\n\n\n\n<p><strong>&#8212; Intentar convertir un valor float a texto<\/strong><br>Select CASE WHEN TRY_CONVERT(float, &#8216;test&#8217;) IS NULL<br>THEN &#8216;Cast ha fallado&#8217;<br>ELSE &#8216;Cast exitoso&#8217;<br>END AS Resultado;<br>go<\/p>\n\n\n\n<p><strong>&#8212; Try_Parse<\/strong><br><strong>TRY_PARSE ( string_value AS data_type [ USING culture ] )<\/strong><\/p>\n\n\n\n<p><strong>&#8212; Convertir a fecha<\/strong><br>SELECT TRY_PARSE(&#8216;Primer d\u00eda trabajo&#8217; AS datetime2 USING &#8216;en-US&#8217;) AS Resultado<br>go<\/p>\n\n\n\n<p><script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script><br><ins class=\"adsbygoogle\" style=\"display: block; text-align: center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-8212748961777604\" data-ad-slot=\"1807313726\"><\/ins><br><script><br \/>\n     (adsbygoogle = window.adsbygoogle || []).push({});<br \/>\n<\/script><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Funciones para conversi\u00f3n de datos Los datos en muchas ocasiones deben ser convertidos y transformados en un est\u00e1ndar para poder tratarlos, para esto se deben usar las funciones de Conversi\u00f3n de datos.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=175\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":1419,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[136,137,138],"class_list":["post-175","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-funcionessql","tag-cast","tag-convert","tag-convertir-datos","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=175"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/175\/revisions"}],"predecessor-version":[{"id":1420,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/175\/revisions\/1420"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/1419"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}