CODEWITHSUNDEEP
sqlsql-serverdatetimenull
Jacob
Jacob

Reputation: 3

Microsoft SQL convert varchar to date, 101, and return blank for null

I need to return a varchar date field as 101 datetime while also returning blank field for null values.

Currently using this:

convert(varchar,try_cast (COALESCE([Col_106],'') as date), 101) as Date_of_Death

but this is returning a default value of 01/01/1900 when the date is NULL. How do I get this to be blank?

I also tried:

Date_of_Death2 = isnull(convert(varchar(10),col_106, 101),'')

but the date function, 101, is not being recognized and not formatting the date how I need it.

Thanks!

Upvotes: 0

Views: 2662

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23807

There is no blank date in SQL Server, whenever you want to cast a blank varchar to a date it would get the default date of 1/1/1900. Instead you can coalesce() later:

select COALESCE(CONVERT(varchar,TRY_CAST( [Col_106] as date), 101),'') as Date_Of_Death2 ...

Upvotes: 1

Related Questions