I have a column in a query that contains some blanks, NULLS and dates (as YYYYMMDD). I need to convert the dates to MM/DD/YYYY, remove the NULLs and keep the blanks blank.
When I try...
isnull(convert(char(10), cast(mytable as datetime), 101), '')
the blanks turn to 01/01/1900, my NULLS become blank and my date converts. Any idea how to keep my blanks blank and not converting to 01/01/1900?
It is confusing to call a column "mytable", so I will call it "DateText" in this answer.
To prevent converting the empty string to '1900-01-01
', you can use the isnull()
function to temporarily map the empty value to null - isnull(DateText, '')
. You already have logic that will map the null value back to an empty string after the conversion.
The updated expression would then be:
isnull(convert(char(10), cast(nullif(DateText, '') as datetime), 101), '')
Results:
DateText | Original | Fixed |
---|---|---|
20240111 | 01/11/2024 | 01/11/2024 |
01/01/1900 | ||
null |
See this db<>fiddle.