관리 메뉴

개발노트

TO_DATE - Convert String to Datetime 본문

DB/Oracle

TO_DATE - Convert String to Datetime

YoonGwon 2022. 10. 24. 17:01

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

 

728x90