Keep null as empty and convert nvarchar to date-CodePudding

expiredDate nvarchar() column contains data like 22-12-2022 00:00:00 and null.

SELECT ISNULL(CONVERT(Date, expiredDate), '') 
FROM tablename

Result for null is default date 1900-01-01 - I want it to be blank or empty.

As the column is getting converted to datetime null is converted to the default date. Required date should be in yyyy-MM-dd format and null as empty

CodePudding user response：

You may use CONVERT here on the raw date to generate a string, and then COALESCE missing null values into empty string:

SELECT expiredDate,
       COALESCE(CONVERT(varchar(10), expiredDate, 120), '') AS expiredDateText
FROM yourTable;

CodePudding user response：

You can try below query.

select case when ISDATE(expiredDate) = 0 then '' else convert(varchar,convert(date,expiredDate)) end as YourColumnName

The ISDATE() function checks whether the input data is a valid date or not. This includes NULL too. It will return Zero (0) if the data is not a date.

If you culture is en-us then by default you will get the date in YYYY-MM-DD format. You can also use Format function in SQL Server to get the date in your expected format.

More information about format function can be found at below link.

https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15