DATENAME Function - SQL Server to Oracle Migration

In Microsoft SQL Server 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.

SQL Server:

  -- 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 SQL Server DATENAME Units to Oracle

You can map SQL Server DATENAME units to the appropriate TO_CHAR format in Oracle as follows:

SQL Server			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 SQL Server 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 IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference

DATENAME Function - SQL Server to Oracle Migration

Mapping SQL Server DATENAME Units to Oracle