DATENAME Function - Sybase ASE to Oracle Migration

In SAP Sybase Adaptive Server Enterprise (ASE) you can use DATENAME function to extract the specified unit (a date part such as year, month, day etc.) from a datetime value. In Oracle you can use TO_CHAR function with the specific format type.

Sybase ASE:

  -- Get the name of week day
  SELECT DATENAME(dw, '2022-12-29')
  # Thursday

Oracle:

  -- Get the name of week day
  SELECT TO_CHAR(DATE '2022-12-29', 'Day') FROM dual;
  # Thursday

Mapping Sybase ASE DATENAME Units to Oracle

You can map Sybase ASE DATENAME units to the appropriate TO_CHAR format in Oracle as follows:

Sybase ASE			Oracle	Output Example
yy	year	DATENAME(yy, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'YYYY')	2022
qq	quarter	DATENAME(qq, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'Q')	4
mm	month	DATENAME(mm, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'MM')	December
wk	week	DATENAME(wk, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'WW')	52
dd	day	DATENAME(dd, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'DD')	29
dy	dayofyear	DATENAME(dy, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'DDD')	363
dw	weekday	DATENAME(dw, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'Day')	Thursday
hh	hour	DATENAME(hh, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'HH24')	13
mi	minute	DATENAME(mi, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'MI')	31
ss	second	DATENAME(ss, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'SS')	11
ms	millisecond	DATENAME(ms, GETDATE())	TO_CHAR(SYSTIMESTAMP, 'FF3')	777

For more information, see Sybase ASE to Oracle Migration.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference

DATENAME Function - Sybase ASE to Oracle Migration

Mapping Sybase ASE DATENAME Units to Oracle