Recently a post was made on LinkedIn asking how one would convert a date stored in a VARCHAR field could be converted to a DATETIME so date math could be performed.  Unfortunately, this problem is more common than it should be.  If users are given a free-form field to enter data, they’ll often enter unreliable data.  This is never intentional on the users’ part, it’s just a matter of course.

These are just a few of the problem cases you can face when trying to convert free form dates into DATETIME.  Let’s consider each of these problems one at a time.

Non-numeric data

If the form were completely open, and they could type anything in, you will see some entries like our “asdf” example.  You may have seen the LIKE comparator used in a previous post.  If so, then the following code may look familiar.  @varcharDateField is just one value from our examples table.

IF PATINDEX('%[^0-9]\/%', @varcharDateField) > 0
BEGIN
 SELECT 'Non-numeric data found.'
END

What this will do is look for the position of the first character that is not 0 through 9, or a forward slash, or a backslash.  If it finds any other character, then it will trigger the part in the BEGIN .. END segment.  If we hit this point, we need to decide how to handle it.   This leads us into the next problem case.

Empty String or NULL

Just like the case where the user gave us a non-numeric value, we need to handle the case where no data was provided.  This is where I usually will ask the business users to provide me a “default” case.  In this situation, I’m going to say the business requirements will allow us to leave NULL as the default case.  If the field is non-numeric or blank, we will default to NULL instead.  But how can you detect a blank string?  I usually check the length of the string to determine if the string is blank.

IF LEN(@varcharDateField) < 1
BEGIN
   SELECT 'Blank string found.'
END

MM/DD/YYYY or DD/MM/YYYY?

The next cases we need to deal with are date format issues.  Did the user mean month then day, or day then month.  In the case of 3/10/1977 or 10/3/1977 both are valid.  But if I were entering my birthdate, only one is valid.  This is a case where you can’t really detect which case is right and wrong.

As a database programmer, you would inform the interface programmer, to put a note next to the field to choose one or the other as default.  Either month then day, or day then month.  Once you and the interface programmer come to a choice, you can then detect for error cases.  For the following detection, we’re going to say the assumption is MM/DD/YYYY.

Given months can only be 01-12, we can make sure the first two digits are in that range.  There are several ways to do that, but let’s take one of the easiest.

IF LEFT(@varcharDateField, 2) > 12
BEGIN
 SELECT 'String could be DD/MM/YYYY.'
END

Given the dates can go either way, we will have to build our query to assume the string is MM/DD/YYYY, unless the first two characters are greater than 12, then we will try DD/MM/YYYY instead.

Other Cases

While there are many other cases we could face, we can handle them all with the TRY..CATCH construct.  This was introduced with SQL 2005, but if you’ve worked with other languages you should recognize it.  This construct allows you to attempt something, and then catch the exception and either address the issue, or fail gracefully (instead of a critical failure.)  We can use the TRY..CATCH to attempt to case the VARCHAR as a DATETIME, and if that fails, we can fall back to our default case.

BEGIN TRY
  SELECT CONVERT(datetime, @varcharDateField) AS DateTimeField
END TRY
BEGIN CATCH
  SELECT NULL AS DateTimeField
END CATCH

Putting it all Together

Given each of these techniques, you could now build a SQL statement capable of converting most dates stored as VARCHAR into DATETIME.  While this is not exhaustive, you should have a good start.  Personally if I were building this, I would build this statement as a User Defined Function,  but you cannot use TRY..CATCH inside a user defined function.  So in order for you  be sure you were doing it the same way each time, you may try creating a PROCEDURE.

As always, if you have any questions feel free to send them in!

References

• PATINDEX
• LEN
• LEFT
• TRY .. CATCH

 

Related posts:

1. Sorting Numbers in VarChar field
2. SQL Azure DATETIME Functions
3. New DATETIME Data Types in 2008
4. Exception Handling
5. SQL 201 – String Manipulation Functions