Try_Convert Datetime showing '1/1/1900', want to show space

Question

How do I get try_convert datetime to display NULL when the input is space?

I am getting two different results here. My intention is to show 'space' number query as Null also, just like first query.

select try_convert(datetime,'abcd')   ---> Null

select try_convert(datetime,'')    ---> '1/1/1900'

Answer 1

You can use NULLIF() to force a NULL when the value is an empty space.

SELECT TRY_CONVERT(datetime, NULLIF('', ''));

Assuming this is actually coming from a column or parameter/variable:

SELECT TRY_CONVERT(datetime, NULLIF(@param, ''));

SELECT TRY_CONVERT(datetime, NULLIF(column, '')) FROM ...;