Change NULL values in Datetime format to empty string

Question

I have a table which contains 'NULL' values which are of type 'Datetime'. Now i have to convert those into empty string but when when i use convert function

ISNULL( [Accrued Out of Default] ,'' ) 

here accrued into default is of datetime type, what it does it changes null to '1900-01-01 00:00:00.000' instead of empty

Then i try to convert them into varchar and apply same

ISNULL(CONVERT(varchar(50),  [Amort Into Default] ),'') 

Now I am able to convert into empty string but now those datetime are converted to string which I needed in datetime So I try to CAST, CONVERT but non of them works.

CONVERT(Datetime,'ISNULL(CONVERT(varchar(50),  [Amort Into Default] ),'')',120) 

This gives error.

Is there any possible solution to this.

> **Solution Hi someone answered this to do as. >      ISNULL(CONVERT(varchar(50),  [Amort Into Default] ,120),'') and it works  I dont know why . 

**

Answer 1

CASE and CAST should work:

CASE WHEN mycol IS NULL THEN '' ELSE CONVERT(varchar(50), mycol, 121) END 

Answer 2

using an ISNULL is the best way I found of getting round the NULL in dates :

ISNULL(CASE WHEN CONVERT(DATE, YOURDate) = '1900-01-01' THEN '' ELSE CONVERT(CHAR(10), YOURDate, 103) END, '') AS [YOUR Date]