CODEWITHSUNDEEP
sqlt-sql
Lucas_Santos
Lucas_Santos

Reputation: 4740

CASE statement using DateTime column

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

IF the column DateDelivered is not null, then shows the date, else, show the string 'Pendent' how can I do that ?

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 query

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

Upvotes: 3

Views: 15740

Answers (2)

Gerardo Lima
Gerardo Lima

Reputation: 6712

The value returned by a CASE operator is the same type and size of the first result clause. As you cannot convert 'Pending' to a DateTime value, you must convert the date values to theirs varchar representation:

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

In this example you can get the same results using a ISNULL:

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

Note 1: when converting date values to varchar, I always prefer YYYY-MM-DD order, since it's not an ambiguous format.

Note 2: note that the misuse CASE can lead to more subtle bugs when it truncates varchar values based on the size of the first result clause:

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

Upvotes: 2

zimdanen
zimdanen

Reputation: 5626

You're trying to convert 'Pendent' to be a DateTime, because it's going to convert all of the data to be the same data type. Also, if you're just checking for NULL, you can use ISNULL or COALESCE. Try this:

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

You can also specify your format for DateDelivered:

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

See more options for 101 here.

Upvotes: 6

Related Questions