Conversión no determinista de las cadenas de fecha literales en valores DATE

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

Tenga cuidado al permitir la conversión de las cadenas de caracteres en tipos de datos DATE. El motivo es que estas conversiones a menudo son no deterministas.

Para controlar estas conversiones no deterministas debe tener en cuenta la configuración de SET LANGUAGE (ESTABLECER IDIOMA) y SET DATEFORMAT (ESTABLECER FORMATO DE FECHA).

Ejemplo de SET LANGUAGE: nombre del mes en polaco

  • SET LANGUAGE Polish;

Una cadena de caracteres puede ser el nombre de un mes. ¿Pero el nombre está en inglés, polaco, croata o en otro idioma? Y, ¿se establecerá la sesión del usuario en el idioma correcto correspondiente?

Por ejemplo, considere la palabra listopad, que es el nombre de un mes. Qué mes es depende del idioma que el sistema SQL considera que se usa:

  • Si es polaco, listopad se traduce como el undécimo mes (noviembre en español).
  • Si es polaco, listopad se traduce como el décimo mes (octubre en español).

Ejemplo de código de SET LANGUAGE

--SELECT alias FROM sys.syslanguages ORDER BY alias;

DECLARE @yourInputDate  NVARCHAR(32) = '28 listopad 2018';

SET LANGUAGE Polish;
SELECT CONVERT(DATE, @yourInputDate) AS [SL_Polish];

SET LANGUAGE Croatian;
SELECT CONVERT(DATE, @yourInputDate) AS [SL_Croatian];

SET LANGUAGE English;


/***  Actual output:  For the two months, note the 11 versus the 10.
SL_Polish
2018-11-28

SL_Croatian
2018-10-28
***/

Ejemplo de SET DATEFORMAT

  • SET DATEFORMAT dmy;

El formato dmy anterior dice que el significado de la cadena de fecha de ejemplo “01-03-2018” se interpretaría como el primer día de marzo del año 2018.

Si en su lugar se ha especificado mdy, la misma cadena “01-03-2018” significaría el tercer día de enero de 2018.

Y si se ha especificado ymd, no hay ninguna garantía de cuáles serían los resultados. El valor numérico de “2018” es demasiado grande para ser un día.

Países o regiones específicos

En Japón y China, se utiliza el DATEFORMAT ymd. Los elementos del formato están en una secuencia que va de la unidad mayor a la menor. Por lo tanto, este formato es útil cuando se quiere ordenar. Este formato se considera el formato internacional. Es internacional porque los cuatro dígitos del año son inequívocos y ningún país o región en la Tierra usa el formato arcaico ydm.

En otros países o regiones, como Alemania y Francia, DATEFORMAT es dmy, esto es, "dd-mm-aaaa". El formato dmy no es adecuado para ordenar, pero es una secuencia razonable de la unidad más pequeña a la mayor.

Los Estados Unidos y los Estados Federados de Micronesia son los únicos países o regiones donde se utiliza mdy, que no es adecuado para ordenar. La secuencia mixta del formato coincide con un patrón para decir fechas en comunicación verbal.

Ejemplo de código de SET DATEFORMAT: mdy frente a dmy

El ejemplo de código de Transact-SQL siguiente usa la misma cadena de caracteres de fecha con tres opciones distintas de DATEFORMAT. Una ejecución del código genera el resultado que se muestra en el comentario:

DECLARE @yourDateString NVARCHAR(10) = '12-09-2018';
PRINT @yourDateString + '  = the input.';

SET DATEFORMAT dmy;
SELECT CONVERT(DATE, @yourDateString) AS [DMY-Interpretation-of-input-format];

SET DATEFORMAT mdy;
SELECT CONVERT(DATE, @yourDateString) AS [MDY-Interpretation-of-input-format];

SET DATEFORMAT ymd;
SELECT CONVERT(DATE, @yourDateString) AS [YMD-Interpretation--?--NotGuaranteed];


/***  Actual output:
12-09-2018  = the input.

DMY-Interpretation-of-input-format
2018-09-12

MDY-Interpretation-of-input-format
2018-12-09

YMD-Interpretation--?--NotGuaranteed
2018-12-09
***/

En el ejemplo de código anterior, el último ejemplo tiene una discrepancia entre el formato ymd y la cadena de entrada. El tercer nodo de la cadena de entrada representa un valor numérico que es demasiado grande para ser un día. Microsoft no garantiza el valor de salida de este tipo de errores.

CONVERT ofrece códigos explícitos para control determinista de formatos de fecha

El artículo de documentación de CAST y CONVERT enumera códigos explícitos que puede usar con la función CONVERT para controlar de forma determinista las conversiones de fecha. Cada mes el artículo está entre nuestras páginas más visitadas.

Nivel de compatibilidad 90 o superior

En SQL Server 2000, el nivel de compatibilidad era 80. Para configuraciones del nivel de 80 o inferiores, las conversiones de fecha implícita eran deterministas.

A partir de SQL Server 2005 y su nivel de compatibilidad 90, las conversiones de fecha implícitas pasaron a ser no deterministas. Las conversiones de fecha se convirtieron en dependientes en SET LANGUAGE y SET DATEFORMAT a partir del nivel 90.

Unicode

La conversión de los datos de caracteres no Unicode entre intercalaciones también se considera no determinista.

Consulte también