SQL 2012: Let’s Make a Date with DATEFROMPARTS and TRY_PARSE!

Continuing on from my last post, I felt like cleaning up some more data and using more SQL Server 2012 features. I happened on DATEFROMPARTS on MSDN and felt like playing with it.

Often when moving data between systems, we’ll receive data in formats that need to be converted to be properly stored and used. I see a lot of value in the new DATEFROMPARTS feature.
First, we need to create a table and get some sample data to play with. We can do so with the following code:

This code will create two tables: one to store our sample “dates” and one to insert the correctly converted date data into. Our sample data will be stored in tblDateTimeToConvert as character data in the format YYYYMMDD. To make things a bit more interesting, rather then inserting the straight numbers, I’ve added some “junk” characters and steps to fix them.

After the table is populated, you can view it with a simple select query: Select * from tblDateTimeToConvert.

If you’re lucky, your data will be uniform. I’m rarely that lucky so I’ve laced the data with junk. I tend to look at the data for patterns, then write code to clean it up. I started simply with:

Now that’s all fine and dandy, but in reading further on MSDN, we also get the new PARSE (and TRY_PARSE) statements. I decided to play with it. I realize this isn’t necessarily the intended use, but just for fun, what would those statements look like?

First off, I tried this:

Select try_parse(col1 as datetime)
from tblDateTimeToConvert

It didn’t work. Instead, it returned an error when using PARSE and NULLs with TRY_PARSE, so I knew something was up. I decided to actually read about the command and fed it my chopped up date, which worked to convert the data.