TO_DATE - Convert String to Datetime - Oracle to SQL Server Migration

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);

Oracle TO_DATE to SQL Server conversion summary:

Note that TRY_CONVERT function is available since SQL Server 2012.

Oracle TO_DATE supports the following format specifiers:

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.

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

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

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

Conversion examples:

Some Oracle formats require using more complex expressions or even modifying the source string:

For more information, see Oracle to SQL Server Migration.