user979331 user979331 - 6 months ago 12
SQL Question

SQL if date is null, have empty varchar, if not null convert date

Here is a section of my query:

CASE WHEN COALESCE(V_CONSTAT_ACTUAL_DATES.ID187, '') = '' THEN ' ' ELSE CONVERT(date, V_CONSTAT_ACTUAL_DATES.ID187) END AS 'actualFinish'


What I am trying to do here is say if this date is null or empty then have ' ' else convert it to a date, but when I have a null value it returns
1900-01-01
how come?

I have even tried CONVERT(varchar, ' ') and that didn't do anything?

Answer

Nulls exist for a reason. NULL is the emptyset, whereas '' means something that has been mapped to 0 which is 1900-01-01 as a date.

You can convert the date to a varchar to workaround this:

CASE WHEN COALESCE(V_CONSTAT_ACTUAL_DATES.ID187, '') = '' THEN ' ' ELSE  CONVERT(varchar,CONVERT(date, V_CONSTAT_ACTUAL_DATES.ID187)) END AS 'actualFinish'

Or best practice is to keep the nulls in your actualFinish column so that you can maintain the column type of date.