Over this week we’ve looked at the difference between Oracle and SQL Server from a few different angles. We’ve looked at date formatting, end of month, top rows, and pagination.

Finishing out the week  I’d like to return to dates and discuss how each platform can create a date from a string. This sounds easy but it’s not as simple as it sounds, well at least not for SQL Server.

SQL Server

There are many ways to create a date from a string. First you’ve got the CONVERT() function and the CAST() function. Next you’ve got DATEFROMPARTS(), DATETIMEFROMPARTS(), DATETIME2FROMPARTS(), SMALLDATETIMEFROMPARTS(), TIMEFROMPARTS(), and DATETIMEOFFSETFROMPARTS().

That’s a lot of functions for one simple task isn’t it? To be fair, it’s really more than 1 simple task. Each of these functions is meant to be paired with the matching data type so you get just what you want. To go along with these you also have the ISDATE() function which tests the value to be sure it’s a date.

CONVERT() is straight forward but there’s a catch. Let’s say you want to convert a string of “MMDDYY” to date.

SELECT CONVERT(date, ‘070903‘)

Result:

2007-09-03

This is the result you get weather you use the format parameter or not. So now you’re stuck parsing this and passing the values to one of the FROMPARTS functions to build your date. This is where Oracle shines.

Oracle

The TO_DATE() function in Oracle allows a date format to be passed, making date conversion really easy. Let’s see it in action.

SELECT to_date(‘070903’, ‘mmddyy’) FROM dual;

Result:

09-JUL-03

Since we can pass a date format the types of strings we can pass to the TO_DATE() functions are near endless. How about ‘HHMMMIDDSSYYYY’?

SELECT to_date(‘02073909532003’, ‘mmddyy’) FROM dual;

Result:

09-JUL-03

Clearly you’d be placed on some sort of medication if you were storing your dates in HHMMMIDDSSYYYY string format but the greatness that comes out of this is that no matter the format you can convert to a date as long as you know the pattern.

Conclusion

While SQL Server gives you a shopping cart full of functions for converting strings to date and various other date flavored data types, it still doesn’t muster up to Oracle’s TO_DATE() function. Something tells me I should write some sample TO_DATE() code for SQL Server and perhaps that’s what I’ll do.

 

If you enjoyed this post and would like to read more check out my blog: SQLTechBlog.com. You can also find more at Twitter, LinkedIn, and Instagram.