Geek asks and answers

CASE statement using DateTime column

I have a datetime column in my query and I want to do CASE in this query.

IF the DateDelivered column is not null and then shows the date, else shows the line "Pendent" how can I do this?

CASE WHEN DateDelivered IS NOT NULL THEN DateDelivered ELSE 'Pendent' END AS DateDelivered 2

I got this error

Conversion failed when converting date and/or time from character string.

Whole request

SELECT  San_Imovel.Imovel_Id ,
        San_Chave.DataHoraPegou ,
        CASE WHEN San_Chave.DateDelivered IS NOT NULL THEN San_Chave.DateDelivered
             ELSE ISNULL(CAST(San_Chave.DateDelivered AS VARCHAR), 'Pendent')
        END AS DateDelivered2 ,
        San_Usuario.NomeCompleto + ' - ' + sc.Apelido AS Nome ,
        San_Cliente.NomeCompleto AS NomeCliente ,
        San_Credenciada.Apelido ,
        San_Cliente.Cliente_Id ,
        San_ChaveImovel.QuantidadeChave
FROM    San_ChaveImovel
        JOIN San_Chave ON San_ChaveImovel.Chave_Id = San_Chave.Chave_Id
        JOIN San_Credenciada ON San_Chave.Credenciada_Id = San_Credenciada.Credenciada_Id
        JOIN San_Imovel ON San_ChaveImovel.Imovel_Id = San_Imovel.Imovel_Id
        JOIN San_Usuario ON San_Chave.Usuario_Id_Responsavel = San_Usuario.Usuario_Id
        JOIN San_Credenciada sc ON San_Usuario.Credenciada_Id = sc.Credenciada_Id
        JOIN San_Cliente ON San_Chave.Cliente_Id = San_Cliente.Cliente_Id
+3
source share
2 answers

You are trying to convert "Pendent" to DateTime because it converts all data to the same data type. Also, if you just check for NULL, you can use ISNULL or COALESCE. Try the following:

ISNULL(CAST(DateDelivered AS VARCHAR), 'Pendent')

You can also specify your own format for DateDelivered:

ISNULL(CONVERT(VARCHAR, DateDelivered, 101), 'Pendent')

Read more about 101 here .

+6
source

, CASE, . "" DateTime, varchar:

CASE WHEN DateDelivered IS NOT NULL THEN CONVERT(VARCHAR(10), DateDelivered, 120)
ELSE 'Pendent'
END AS DateDelivered

, ISNULL:

ISNULL(CONVERT(VARCHAR(10), DateDelivered, 120), 'Pending') AS [DateDelivered]

1: varchar YYYY-MM-DD order, .

2: , CASE , varchar :

CASE WHEN 1=0 THEN 'A'
ELSE 'BBB'
END -- returns only 'B' !!!
+2

More articles:


All Articles