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.
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‘)
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.
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;
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;
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.
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.
3 thoughts on “SQL Server vs Oracle: Convert string to date”
[…] Daniel Janik compares Oracle and SQL Server date conversion functions: […]
This is a bit off the cuff since I’m coming off a long night of maintenance, but did you forget about the format codes available in the Convert () function of Sql Server? I do agree that date and datetime data types can be a pain but let’s compare all the parts not just some.
Hi Richard, Sorry for the confusion and thank you for the comment. I did mention the format codes in an earlier post when discussing converting dates to a string (https://sqltechblog.com/2017/08/07/sql-server-vs-oracle-date-formatting/). This post is going the other direction and when using convert() to go to a date the codes don’t work. SQL assumes ‘YYMMDD’ and in some cases this could be entirely wrong.
For instance, try the following:
Select 0 As FormatCode, Convert(date,’070903′,0) As DateResult Union All
Select 1, Convert(date,’070903′,1) Union All
Select 2, Convert(date,’070903′,2) Union All
Select 3, Convert(date,’070903′,3) Union All
Select 4, Convert(date,’070903′,4) Union All
Select 5, Convert(date,’070903′,5) Union All
Select 6, Convert(date,’070903′,6) Union All
Select 7, Convert(date,’070903′,7) Union All
Select 8, Convert(date,’070903′,8) Union All
Select 9, Convert(date,’070903′,9) Union All
Select 10, Convert(date,’070903′,10) Union All
Select 11, Convert(date,’070903′,11) Union All
Select 12, Convert(date,’070903′,12) Union All
Select 13, Convert(date,’070903′,13) Union All
Select 14, Convert(date,’070903′,14) Union All
Select 20, Convert(date,’070903′,20)
I hope that helps. 🙂