CONVERT - Datetime to String - Sybase ASE to Oracle Migration

In Sybase Adaptive Server Enterprise (ASE) 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 Sybase ASE CONVERT and Oracle formats are different.

Sybase ASE:

  -- 3rd parameter specifies 121 style ('YYYY/MM/DD HH:MI:SS' format)
  SELECT CONVERT(VARCHAR, GETDATE(), 121);
  # 2022/12/27 14:33:49

Oracle:

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

Mapping Sybase ASE Datetime Style to Oracle Format

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

Sybase ASE 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
121 'YYYY/MM/DD HH24:MI:SS' 2022/12/27 14:33:49

Conversion examples:

Sybase ASE 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(), 121) TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS')

For more information, see Sybase ASE to Oracle Migration.