SQL Server vs Oracle: Convert string to date

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.

3 thoughts on “SQL Server vs Oracle: Convert string to date

  1. 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.

    Like

  2. 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. 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s