TO_DATE - Convert String to Datetime

In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In SQL Server, you can use CONVERT or TRY_CONVERT function with an appropriate datetime style.

Oracle:

  -- Specify a datetime string and its exact format
  SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;

SQL Server:

  -- Specify a datetime string and style 102 (ANSI format), raises an error if conversion fails
  SELECT CONVERT(DATETIME, '2012-06-05', 102);
 
  -- TRY_CONVERT available since SQL Server 2012 (returns NULL if conversion fails)
  SELECT TRY_CONVERT(DATETIME, '2012-06-05', 102);

TO_DATE Conversion Overview

Oracle TO_DATE to SQL Server conversion summary:

	Oracle	SQL Server
Syntax	TO_DATE(string, format)	CONVERT(DATETIME, string, style)
		TRY_CONVERT(DATETIME, string, style)
Default Format	Specified by NLS_DATE_FORMAT	Recognizes many formats

Note that TRY_CONVERT function is available since SQL Server 2012. 

TO_DATE Format Specifiers

Oracle TO_DATE supports the following format specifiers:

Oracle TO_DATE	Format Specifier
YYYY	4-digit year
YY	2-digit year
RRRR	4-digit or 2-digit year, 20th century used for years 00-49, otherwise 19th
MON	Abbreviated month (Jan - Dec)
MONTH	Month name (January - December)
MM	Month (1 - 12)
DY	Abbreviated day (Sun - Sat)
DD	Day (1 - 31)
HH24	Hour (0 - 23)
HH or HH12	Hour (1 - 12)
MI	Minutes (0 - 59)
SS	Seconds (0 - 59)

Converting Oracle TO_DATE to SQL Server

Unlike Oracle TO_DATE function that allows you to build any format string using format specifiers (YYYY and MM i.e.), in SQL Server, you have to use a datetime style that defines the format for the entire datetime string. 

Fortunately, most applications use typical datetime formats in Oracle that can be easily mapped to a datetime format style in SQL Server.

Difference Between CONVERT and TRY_CONVERT in SQL Server

You can use both CONVERT and TRY_CONVERT functions to convert a string to a datetime value.

CONVERT raises an error when it cannot recognize the format, while TRY_CONVERT returns NULL in this case:

SQL Server:

  -- Specify not valid datetime string
  SELECT CONVERT(DATETIME, 'ABC');
  # Msg 241, Level 16, State 1, Line 1
  # Conversion failed when converting date and/or time from character string.
 
  SELECT TRY_CONVERT(DATETIME, 'ABC');
  # NULL

CONVERT and TRY_CONVERT Recognize ANSI/ISO and US Formats by Default

Note that when converting a string to datetime, both CONVERT and TRY_CONVERT recognize ANSI/ISO datetime formats with various delimiters by default, so you do not need to specify a style for them.

An ANSI/ISO format is year, month, day, hour, minute, seconds, fractional seconds (YYYY-MM-DD HH24:MI:SS.FFF) where trailing parts can be omitted so you can specify YYYY-MM-DD, or YYYY-MM-DD HH24:MI etc.

SQL Server:

  -- ISO date formats with various delimiters recognized by default (year, month, day)
  SELECT CONVERT(DATETIME, '2012-06-30');
  SELECT CONVERT(DATETIME, '2012/06/30');
  SELECT CONVERT(DATETIME, '2012.06.30');
 
  SELECT CONVERT(DATETIME, '2012-06-30 11:10');
  SELECT CONVERT(DATETIME, '2012-06-30 11:10:09');
  SELECT CONVERT(DATETIME, '2012-06-30 11:10:09.333');
  SELECT CONVERT(DATETIME, '2012/06/30 11:10:09.333');
  SELECT CONVERT(DATETIME, '2012.06.30 11:10:09.333');
 
  -- ISO date without delimiters is also recognized
   SELECT CONVERT(DATETIME, '20120630');

SQL Server also recognizes United States datetime format (month, day, year and time) by default, so you do not need to specify style 101:

SQL Server:

  -- United States date formats with various delimiters recognized by default (month, day, year)
  SELECT CONVERT(DATETIME, '06-30-2012');
  SELECT CONVERT(DATETIME, '06/30/2012');
  SELECT CONVERT(DATETIME, '06.30.2012');
 
  SELECT CONVERT(DATETIME, '06-30-2012 11:10');
  SELECT CONVERT(DATETIME, '06/30/2012 11:10:09');
  SELECT CONVERT(DATETIME, '06.30.2012  11:10:09.333');

Also SQL Server recognizes the following formats by default:

SQL Server

  SELECT CONVERT(DATETIME, '17-FEB-2013');
  # 2013-02-17 00:00:00.000

Mapping Oracle TO_DATE Formats to SQL Server CONVERT Style

You can map an Oracle TO_DATE format to SQL Server CONVERT or TRY_CONVERT style as follows:

	Oracle TO_DATE Format	SQL Server CONVERT and TRY_CONVERT Style
1	YYYY-MM-DD	Default (no style specified), 101, 102, 110, 111, 20, 120, 21 and 121
2	YYYY/MM/DD	Default, 101, 102, 110, 111, 20, 120, 21 and 121
3	DD/MM/YYYY	103
4	YYYY-MM-DD HH24:MI:SS	Default, 101, 102, 110, 111, 20, 120, 21 and 121
5	MM/DD/YYYY HH24:MI:SS	Default and 101
6	DD-MON-YYYY	Default, 106 and 113

Conversion examples:

	Oracle	SQL Server
1	TO_DATE('2012-07-18', 'YYYY-MM-DD')	CONVERT(DATETIME, '2012-07-18')
2	TO_DATE('2012/07/18', 'YYYY/MM/DD')	CONVERT(DATETIME, '2012/07/18')
3	TO_DATE('24/07/2022', 'DD/MM/YYYY')	CONVERT(DATETIME, '24/07/2022', 103)
4	TO_DATE('2012-07-18 13:27:18', 'YYYY-MM-DD HH24:MI:SS')	CONVERT(DATETIME, '2012-07-18 13:27:18')
5	TO_DATE('07/18/2012 13:27:18', 'MM/DD/YYYY HH24:MI:SS')	CONVERT(DATETIME, '07/18/2012 13:27:18')
6	TO_DATE('17-FEB-2013', 'DD-MON-YYYY')	CONVERT(DATETIME, '17-FEB-2013')