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
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.