I have an Oracle SQL script that I'm converting to run in MS SQL Server. The script has the to_date function in it and uses the RR date format. Here's the function:
to_date('07-AUG-14','DD-MON-RR')
I'm going to use the MS SQL Server function CONVERT like this
CONVERT(DATETIME, '07-AUG-14', num)
where num is the code of the format I need.
What code should I use in MS SQL Server to get the same type of functionality as the Oracle RR format?
For a U.S. datetime conversion, you can use:
CONVERT(DATETIME, '07-AUG-14', 10)
-- ^^
For other cultures, style (the 3rd argument) will differ; but style needs to be a Without century (yy) value for your purpose.
The semantics of Oracle's RR date format apply to SQL Server's yy date styles too. CAST and CONVERT (Transact-SQL) (for SQL Server 2008) on MSDN explains...
By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950.
...and lists the numeric values for the Without century (yy) date styles.
(For reference, another SO answer explains the equivalent semantics of Oracle's RR date format.)
You can more broadly confirm the default semantics of SQL Server's (U.S.) yy date style with the following queries...
SELECT CONVERT(DATETIME, '07-AUG-14', 10)
SELECT CONVERT(DATETIME, '07-AUG-49', 10)
SELECT CONVERT(DATETIME, '07-AUG-50', 10)
SELECT CONVERT(DATETIME, '07-AUG-51', 10)
..., which yield...
2014-08-07 00:00:00.000 -- This century assumed.
2049-08-07 00:00:00.000 -- This century assumed.
1950-08-07 00:00:00.000 -- Last century assumed.
1951-08-07 00:00:00.000 -- Last century assumed.
...with a default two-digit year cutoff configuration. (yy date styles for other cultures should behave similarly.)
