SQL Server conversion error when using IIF on datetime field to output a string

Question

I have the following select statement:

SELECT IIF(DateFirstSeen IS NOT NULL, DateFirstSeen, 'Not Seen') as [Date First Seen]

But when I run this, I get the error:

Conversion failed when converting character string to smalldatetime data type

Can anybody help with this?

Does this answer your question? [How to convert DateTime to VarChar](https://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar) — demo, Mar 05 '20 at 14:55
As per Demo's comment; IIF expects the second and third parameters to be the same type, if they're not one has to be converted to the other. In your case the IIF sees the SMALL DATETIME parameter, assumes that's the data type you want to return, then encounters the string, which can't be converted to a SMALLDATETIME. So, if you want the IIF to return a string, give it two strings... To do that, convert the DateFirstSeen value to a string... — MatBailie, Mar 05 '20 at 14:58
No, the conversion is simple enough, it's the IIF that is causing the problems, as pointed out below, both branches of IIF() must return the same datatype — necrofish666, Mar 05 '20 at 14:59

score 5 · Accepted Answer · answered Mar 05 '20 at 14:54

5

Both branches of IIF() must return the same datatype, while you are mixing a date and a string.

Consider converting the date to string first, for example:

 COALESCE(CONVERT(varchar, DateFirstSeen, 23), 'Not Seen')

Note that, when dealing with nulls, COALESCE() shortens the logic compared to IIF().

answered Mar 05 '20 at 14:54

GMB

216,147
25
84
135

You could also use the ISNULL function here :) – CR7SMS Mar 05 '20 at 15:15
@CR7SMS: yes. I like `COALESCE()` better, because it's standard SQL - so most databases support it. – GMB Mar 05 '20 at 15:16

score 2 · Answer 2 · answered Mar 05 '20 at 14:56

The IIF() returns a single value. But there are competing types. Which type to use? SQL standard emphasize that the most restrictive type is returned, not the most general.

To represent 'Not Seen' you need a string. You should use convert() or format() with the appropriate specification:

SELECT COALESCE(CONVERT(VARCHAR(255), DateFirstSeen, 120), 'Not Seen')

Or, learn to live with and love NULLs.

SQL Server conversion error when using IIF on datetime field to output a string

2 Answers2