I’ve had a few instances where a datetime column gets populated with an empty string. When accessed these come out as either “1900-01-01 00:00:00.000” or “1899-12-30 00:00:00.000”. I was converting these from datetime to date and tried using CASE() to present NULL instead of the incorrect date.
Turns out, if you do the CONVERT() anywhere within the CASE(), it will just pass back the converted value. In the end, I realized I had to CONVERT() outside of the CASE() statement.
convert(date,
case
when ImagesTakenDate = '1899-11-30 00:00:00.000' then null
else ImagesTakenDate
end
) as ImagesTakenDate