¿Cómo corregir el error 'Error de conversión al convertir la fecha y / o la hora a partir de una cadena de caracteres'?

Hay muchos casos en los que las fechas y horas no aparecen en el formato que desea, ni el resultado de una consulta se ajusta a las necesidades de los espectadores. Hay varias características integradas de SQL Server para formatear la cadena de fecha según sus necesidades, pero para que la cadena sea interpretada por SQL Server y para evitar errores de conversión, debe estar en un formato adecuado. Cuando intentamos convertir la fecha o la hora de una cadena de caracteres, a veces surge un error. 'La conversión falló al convertir la fecha y / o la hora de la cadena de caracteres'.

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

El error mencionado anteriormente normalmente surge cuando el literal de fecha no es apropiado y no se puede convertir de la cadena en DateTime o date. Este error se debe a una serie de razones, que analizaremos en detalle junto con el conjunto de soluciones.

Ejemplo 1:

Reino Unido La notación de fecha y hora muestra la fecha usando el formato día-mes-año (10 de enero de 2015 o 1/10/2015) que podemos lograr usando la función 'convertir' de la característica incorporada 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 días y el último año, lo que es incorrecto y no puede ser interpretado por SQL Server, lo que da como resultado un error. El formato correcto para la conversión de fecha al estilo del Reino Unido utilizando el estilo de fecha '103' es 'dd / mm / aaaa'.

Formato erróneo:

Declare @date_time_value varchar (100) = '10 / 16/2015 21:02:04 'seleccione CONVERT (datetime2, @date_time_value, 103) como UK_Date_Time_Style

Figura 2: Formato de fecha incorrecto que genera un error

Formato correcto:

El formato de fecha británico y francés es 103 = 'dd / mm / aaaa' o 3 = 'dd / mm / aa'. Aquí 103 y 3 son estilos de fecha.

Declare @date_time_value varchar (100) = '10 / 1/15 21:02:04 'seleccione CONVERT (datetime2, @date_time_value, 103) como 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 'seleccione CONVERT (datetime2, @date_time_value, 3) como 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, la conversión de cadenas a la fecha en el servidor SQL da como resultado un error, no debido a los formatos de fecha u hora utilizados, sino a que está intentando almacenar información incorrecta que no es aceptable para el esquema.

Fecha equivocada:

La razón del siguiente error es simplemente que en el año 2019 no existe una fecha como “29 de febrero” porque no es un año bisiesto.

Declare @date_time_value varchar (100) = '2019-02-29 21:02:04' seleccione cast (@date_time_value como datetime2) como date_time_value

Figura 5: Error generado porque 2019 no es un año bisiesto, por lo que no tiene el 29 de febrero como fecha

Corriga uno:

Declare @date_time_value varchar (100) = '2019-02-28 21:02:04' seleccione cast (@date_time_value como datetime2) como date_time_value

Figura 6: Fecha correcta

Formato de fecha ISO 8601:

Aunque hay numerosos formatos disponibles para manipular valores de fecha, cuando se trabaja para una masa global / internacional, puede ser un problema de usabilidad elegir una representación de fecha y hora. Por lo tanto, se deben evitar los literales de fecha / hora específicos de la cultura. Si consideramos esta fecha “08/03/2018”, se interpretará de diferentes maneras en diferentes regiones del mundo.

• En el estilo del Reino Unido, se interpreta como '8 de marzo de 2018'.
• En estilo europeo se interpreta como '3 de agosto de 2018'

Afortunadamente, existe una alternativa en el formato de fecha internacional desarrollado por ISO. El formato estándar mundial ISO 8601 “AAAA-MM-DDThh: mm: ss” es una opción más independiente del idioma para los literales de cadena y aborda todos estos problemas. Mientras que 'aaaa' es el año, 'mm' es el mes y 'dd' es el día. Por lo tanto, la fecha '8 de marzo de 2018' en formato ISO internacional se escribe 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' seleccione convertir (datetime2, @ date_time_value, 126) como [aaaa-mm-ddThh: mi: ss.mmm]

Figura 7: Formato de fecha estándar internacional ISO 8601

Recomendaciones:

Con suerte, este artículo ayudará a aliviar la confusión que he visto con frecuencia en la comunidad sobre los valores de fecha y hora. Sin embargo, se recomienda que nunca almacene fechas en tipo de texto (varchar, char, nvarchar, nchar o text). Siempre almacene el valor de fecha en columnas de tipo DATE, DATETIME y preferiblemente DATETIME2 (proporciona más precisión) y deje el formato de información de fecha a la capa de interfaz de usuario en lugar de recuperarse de la base de datos.