CODEWITHSUNDEEP
sql-server
Mike
Mike

Reputation: 133

SQL Server : converting Null date times

I have a query in SSMS which is returning 1900-01-01, how can I use a CASE WHEN accurately to replace 1900-01-01 with '' (a blank not a null). 

CAST(ISNULL(CAST(CONVERT(DATE, cmmt.[CmmtExpirationDate], 101) AS NVARCHAR(20)), '') AS DATE) AS [Cmmt Expiration Date]

Result: 1900-01-01

I tried this but no luck (terrible syntax):

CASE 
   WHEN (CAST(ISNULL(cast(convert(Date, cmmt.[CmmtExpirationDate] , 101) as nvarchar(20)), '') = '1900-01-01')
      THEN ''
      ELSE CAST(ISNULL(cast(convert(Date, cmmt.[CmmtExpirationDate] , 101) as nvarchar(20)),'') AS DATE
END

Upvotes: 1

Views: 1677

Answers (2)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can use try_convert as below:

try_convert(date, case when datecolumn='' then null else datecolumn end)

Upvotes: 0

Heinzi
Heinzi

Reputation: 172468

The result of your expression needs to have a fixed data type. DATE is not possible, since '' is not a valid date. nvarchar(20) would be an option, but that means that your result will be a string even if it is not 1900-01-01.

Once you accept that, the solution is simple:

CASE WHEN cmmt.[CmmtExpirationDate] = '1900-01-01'
     THEN CONVERT(nvarchar(20), cmmt.[CmmtExpirationDate])
     ELSE CONVERT(nvarchar(20), '')
END

You might want to specify the desired output format as a third parameter to the first CONVERT statement.

(I assume that CmmtExpirationDate is of type DATE, because if it isn't, it should have been mentioned in the question.)

Upvotes: 2

Related Questions