CONVERT - Datetime to String - SQL Server to Oracle Migration

In SQL Server, you can use the CONVERT function to convert a DATETIME value to a string with the specified format. In Oracle you can use the TO_CHAR function.

Note that SQL Server CONVERT and Oracle TO_CHAR formats are different.

SQL Server:

  -- 3rd parameter specifies 121 style (ODBC 'YYYY-MM-DD HH:MI:SS.FFF' format with milliseconds)
  SELECT CONVERT(VARCHAR, GETDATE(), 121);
  # 2022-12-27 14:33:49.413

Oracle:

  -- Specify string format using format specifiers
  SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') FROM dual;
  # 2022-12-27 14:33:49.413

Mapping SQL Server Datetime Style to Oracle Format

When you convert CONVERT function to TO_CHAR you have to map the SQL Server style to the appropriate format string in Oracle:

SQL Server Style Oracle Format String Output Example
1 US 'MM/DD/YY' 10/21/24
3 British/French 'DD/MM/YY' 21/10/24
7 'Mon DD, YY' Dec 27, 22
8 Time 'HH24:MI:SS' 14:33:49
101 US 'MM/DD/YYYY' 12/27/2022
103 British/French 'DD/MM/YYYY' 27/12/2022
104 German 'DD.MM.YYYY' 27.12.2022
108 Time 'HH24:MI:SS' 14:33:49
111 Japan 'YYYY/MM/DD' 2024/07/27
112 ISO 'YYYYMMDD' 20221227
120 ODBC 'YYYY-MM-DD HH24:MI:SS' 2022-12-27 14:33:49
121 ODBC with milliseconds 'YYYY-MM-DD HH24:MI:SS.FF3' 2022-12-27 14:33:49.413
126 ISO (no blanks) 'YYYY-MM-DD"T"HH24:MI:SS.FF3' 2024-12-04T23:55:32.113

Conversion examples:

SQL Server Oracle
CONVERT(VARCHAR, GETDATE(), 1) TO_CHAR(NOW(), 'MM/DD/YY')
CONVERT(VARCHAR, GETDATE(), 3) TO_CHAR(NOW(), 'DD/MM/YY')
CONVERT(VARCHAR, GETDATE(), 7) TO_CHAR(SYSTIMESTAMP, 'Mon DD, YY')
CONVERT(VARCHAR, GETDATE(), 8) TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS')
CONVERT(VARCHAR, GETDATE(), 101) TO_CHAR(SYSTIMESTAMP, 'MM/DD/YYYY')
CONVERT(VARCHAR, GETDATE(), 103) TO_CHAR(SYSTIMESTAMP, 'DD/MM/YYYY')
CONVERT(VARCHAR, GETDATE(), 104) TO_CHAR(SYSTIMESTAMP, 'DD.MM.YYYY')
CONVERT(VARCHAR, GETDATE(), 108) TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS')
CONVERT(VARCHAR, GETDATE(), 111) TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD')
CONVERT(VARCHAR, GETDATE(), 112) TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD')
CONVERT(VARCHAR, GETDATE(), 120) TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
CONVERT(VARCHAR, GETDATE(), 121) TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3')
CONVERT(VARCHAR, GETDATE(), 126) TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')

For more information, see SQL Server to Oracle Migration.