SQL Date Conversion from different data types

When we receive data feed from outside vendors, the date values are often like this;

Purchase Date
2020-07-12
NULL
'n/a'
''

The challenge is how to parse these dates and load them in SQL server table. Here is one work around;

DECLARE @purchaseDate nvarchar(10) = '9/30/2020 12:00:00 AM'
--DECLARE @purchaseDate nvarchar(10) = ''
--DECLARE @purchaseDate nvarchar(10) = 'n/a'
--DECLARE @purchaseDate nvarchar(10) = NULL

SELECT 
	CASE 
	WHEN ISDATE(ISNULL(@myDate, NULL)) = 1 THEN TRY_PARSE(@myDate AS date)
	END PurchaseDate

We are basically checking whether value is of date, if yes then we apply transformation logic.

Add to favorites