Migrating questionable date strings

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

SourceColumn OutputData
19/11/2000 2000-11-19
04-21-2010 2010-04-21
2010 June 2010-06-01
Dec-01-2010 2010-12-01
feb 2011 2011-02-01
2008 2008-01-01
xxxxxxxx NULL
NULL
6/10 2016-06-10

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.

Advertisements
This entry was posted in Development, SQL and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s