CONVERT - Datetime to String - SQL Server to Oracle Migration

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

Note that SQL Server CONVERT and Oracle 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
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
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

Conversion examples:

SQL Server Oracle
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(), 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')

For more information, see SQL Server to Oracle Migration.