sqlsql-serverdatetimeisnull

Convert date, turn NULLS blank and keep blanks?


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?


Solution

  • 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.