I’ve been having a lot of fun with data migration lately /s
Anyway I have an evil source database that has free-form strings that allegedly represent dates. I look at this data and think to myself, this is why we use the corect data types and have validation, but I digress….
This data contains such wonderful dates as ‘N/A’, ‘feb 2009’, ‘2008’, ‘09/28/09’ and ‘19/11/2014’
So we have partial dates, mixed date formats and things that just are not dates, which I’d like to be dates or NULLs after migration.
I’ve crafted a bit of SQL that works in my case to perform the conversions that I need. Because of the varing formats of my inputs TRY_PARSE and TRY_CONVERT have differing levels of success, and to add to this when you feed an empty string into TRY_CONVERT you’ll get back ‘1900-01-01’ which I don’t want.
SELECT [Date] as SourceColumn, ISNULL( ISNULL(TRY_PARSE([Date] as DATE), TRY_PARSE([Date] as DATE USING 'en-GB') ), CASE WHEN LEN([Date])=0 THEN NULL ELSE TRY_CONVERT(Date, [Date]) END ) as OutputData FROM [source_table]
The code works as TRY_PARSE returns NULL if it can’t successfully extract a date.
Combined with the IFNULL function this allows us to wrap extra parse attempts using other cultures and then using a CASE Statement to ensure I get NULL if the input string is ‘’
Inside the CASE I optionally use TRY_CONVERT which handles the cases where I have inputs like ‘2009’ and ‘feb 2009’ or other text/partial dates.
Here’s a sample output from the query shown above
Hopefully this saves someone else some time in the future, or if you know of a better way of doing this please do let me know.