¿Cómo convertir un error de conversión fallido en la fecha y / o la hora a partir de un error de cadena de caracteres?

Hay muchos casos en los que las fechas y las horas no aparecen en el formato que desea y la salida de la pregunta no se adapta a las necesidades de la audiencia. Hay algunas características integradas de SQL Server para formatear la cadena de fecha según sus requisitos, pero para interpretar la cadena por SQL Server y evitar errores de conversión, debe estar en el formato correcto. Cuando intentamos convertir una fecha u hora a partir de una cadena de caracteres después de un error, a veces tiene éxito. «La conversión falló al convertir la fecha y / o la hora de la cadena de caracteres».

error de conversión de fecha y hora
Figura 1: Error de conversión de fecha y / o hora de la cadena de caracteres

El error mencionado anteriormente generalmente ocurre cuando la fecha literal es incorrecta y no se puede convertir de la cadena a DateTime o date. Este error surge por varias razones, que discutiremos en detalle junto con la solución corregida.

Ejemplo 1:

La notación de fecha y hora del Reino Unido muestra la fecha usando el formato día-mes (10 de enero de 2015 o 1/10/2015) que podemos lograr usando la función de «conversión» de SQL Server con el estilo de formato 103.

Aquí, en el siguiente ejemplo, podemos ver que la cadena de fecha proporcionada tiene un formato incorrecto. Primero, proporciona el mes, luego los últimos días y años que son incorrectos y no pueden ser interpretados por SQL Server, lo que conduce a un error. El formato correcto para convertir la fecha al estilo del Reino Unido usando el estilo de fecha «103» es «dd / mm / aaaa».

Formato incorrecto:

Declare @date_time_value varchar(100)= '10/16/2015 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as UK_Date_Time_Style
Figura 2: Formato de fecha incorrecto que da como resultado un error

Formato correcto:

El formato de fecha británico y francés es 103 = «dd / mm / aaaa» o 3 = «dd / mm / aa». Aquí están los estilos de fecha y 103 y 3.

Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as Date_Time_Style
Figura 3: Formato de fecha correcto con estilo de fecha británico / francés «dd / mm / aaaa»
Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 3) as UK_Date_Time_Style
Figura 4: Formato de fecha correcto con estilo de fecha británico / francés «dd / mm / aa»

Ejemplo 2:

A veces, una conversión de cable a la fecha en un servidor SQL da como resultado un error, no debido a los formatos de fecha u hora utilizados, sino porque está tratando de almacenar información incorrecta que no es aceptable para el esquema.

Fecha incorrecta:

La razón del siguiente error es que no hay una fecha como «29 de febrero» en 2019 porque no es un año bisiesto.

Declare @date_time_value varchar(100)= '2019-02-29 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value
Figura 5: El error surgido como 2019 no es un año bisiesto, por lo que el 29 de febrero no es una fecha

Cabeza derecha:

Declare @date_time_value varchar(100)= '2019-02-28 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value
Figura 6: Fecha correcta

Formato de fecha ISO 8601:

Si bien hay muchos formatos disponibles para manipular valores de fecha, a medida que trabaja con su masa global / internacional, elegir una representación de fecha puede ser un problema de usabilidad. Por lo tanto, deben evitarse las letras de fecha / hora específicamente relacionadas con la cultura. Si consideramos esta fecha “08/03/2018”, se mostrará de diferentes formas en diferentes regiones del mundo.

  • En el estilo del Reino Unido, se retrata como «8 de marzo de 2018».
  • En estilo europeo se retrata como «3 de agosto de 2018»

Afortunadamente, existe otra opción en el formato de fecha internacional desarrollado por ISO. El formato estándar global ISO 8601 “AAAA-MM-DDThh: mm: ss” es una opción lingüísticamente más independiente para la literatura cableada y aborda todos estos problemas. Como el año es «aaaa», el mes es «mm» y el día es «dd». Por lo tanto, la fecha «8 de marzo de 2018» está escrita en formato internacional ISO como «2018-03-08». Por tanto, el formato ISO es la mejor opción para la representación de fechas.

Declare @date_time_value varchar(100)= '2019-03-28 21:02:04'
select convert(datetime2,@date_time_value,126) as [yyyy-mm-ddThh:mi:ss.mmm]
Figura 7: Formato de fecha estándar internacional ISO 8601

Alabanzas:

Espero que este artículo ayude a aliviar la confusión que a menudo he visto en la comunidad sobre los valores de fecha / hora. Sin embargo, se recomienda no almacenar las fechas en el tipo de texto (varchar, char, nvarchar, nchar o text). Almacenar siempre un valor de fecha en las columnas DATE, DATA y preferiblemente DATETIME2 (proporcionadas con mayor precisión) y dejar el formato de información de fecha. a la capa de interfaz de usuario en lugar de recuperarlo de la base de datos.