tipo - obtener mes y año de una fecha sql

Persistir una columna de fecha y hora calculada en SQL Server 2005

Question

Tengo una columna XML en una tabla; Quiero "promover" un cierto valor en ese XML como una columna calculada e indexarlo para una búsqueda más rápida. Tengo una función que toma la información XML y muestra el elemento de interés, como este:

CREATE FUNCTION [dbo].[fComputeValue] (@data XML)
RETURNS datetime
WITH SCHEMABINDING
AS
BEGIN
  RETURN @data.value(''(/Metadata/Value[@Key="StartDate"])[1]'', ''datetime'')
END 

Sin embargo, cuando intento crear la columna calculada:

ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS ([dbo].[fComputeValue]([CustomMetadataColumn])) PERSISTED

Obtuve el siguiente error:

Msg 4936, nivel 16, estado 1, línea 2 Columna calculada ''StartDate'' en la tabla ''CustomMetadataTable'' no se puede conservar porque la columna no es determinista.

Funciona si yo:

• trabajar con valores varchar, int, double (es decir, distintos de datetime)
• eliminar la palabra clave PERSISTED (pero luego no puedo crear un índice en la columna)

También debo mencionar que los valores de fecha y hora están en formato de fecha y hora de XSD. ¿Algunas ideas? Gracias.

Answer 1

Qué pasa:

CREATE FUNCTION [dbo].[fComputeValue] (@data XML)
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
  RETURN @data.value(''(/Metadata/Value[@Key="StartDate"])[1]'', ''varchar(50)'')
END

y:

ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS (convert(datetime,([dbo].[fComputeValue]([CustomMetadataColumn]), 127)) PERSISTED

o:

return convert(datetime, @data.value(''(/Metadata/Value[@Key="StartDate"])[1]'', ''varchar(50)''), 127)

De libros en línea:

CONVERT es determinista a menos que exista una de estas condiciones:

El tipo de fuente es sql_variant.

El tipo de destino es sql_variant y su tipo de fuente no es determinista.

El tipo de fuente o destino es datetime o smalldatetime, el otro tipo de fuente o destino es una cadena de caracteres, y se especifica un estilo no determinista. Para ser determinista, el parámetro de estilo debe ser una constante. Además, los estilos inferiores o iguales a 100 no son determinísticos, a excepción de los estilos 20 y 21. Los estilos superiores a 100 son deterministas, excepto los estilos 106, 107, 109 y 113.

Podría ser útil si usa CONVERT con el estilo 127