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.
| Examples |
|---|
| 22/10/2010 |
| 1/2/3 |
| asdf |
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!