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.

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

CONVERT - Datetime to String - SQL Server to Oracle Migration

Mapping SQL Server Datetime Style to Oracle Format