Eu tenho um único char (8) variável formatada como ddmmyyyy em um procedimento armazenado (qualidade e validade deste valor é desconhecida e além do meu controle). Qual é a melhor maneira mais eficiente de mover o valor para uma variável datetime e lançar um erro se não for datetime válido.

DECLARE @Source       char(8)
DECLARE @Destination  datetime

SET @Source='07152009'

--your solution here


SELECT @Destination

aqui é a melhor maneira que eu poderia pensar:

DECLARE @Source             char(8)
DECLARE @Temp               varchar(10)
DECLARE @Destination        datetime

set @Source='07152009'
SET @Temp=LEFT(@Source,2)+'/'+SUBSTRING(@Source,3,2)+'/'+RIGHT(@Source,4)

IF ISDATE(@Temp)!=1
BEGIN
    RAISERROR('ERROR, invalid date',16,1)
END
SET @Destination=@Temp

SELECT @Source AS Source, @Temp AS  Temp, @Destination AS Destination

EDITAR aqui está o que eu vou com ...

DECLARE @Source             char(8)
DECLARE @Destination        datetime

set @Source='07152009'
BEGIN TRY
    SET @Destination=CONVERT(datetime,RIGHT(@Source,4)        -- YYYY
                                      +LEFT(@Source,2)        -- MM
                                      +SUBSTRING(@Source,3,2) -- DD
                             )
END TRY
BEGIN CATCH
    PRINT 'ERROR!!!' --I'll add a little more logic here and abort processing
END CATCH

SELECT @Source AS Source, @Destination AS Destination