2 Ways to Get the Day from a Date in Oracle

Posted on by Ian

Below are two functions that can be used to return the day from a date in Oracle Database.

The EXTRACT() Function

The EXTRACT(datetime) function is used for extracting various datetime parts from a datetime value. This includes the day.

Here’s an example:

SELECT EXTRACT(DAY FROM DATE '2037-10-25')
FROM DUAL;

Result:

25

It’s the DAY keyword that extracts the day part from the date. We can get other date parts by changing it to the relevant keyword. For example, YEAR, MONTH, HOUR, MINUTE, etc.

We can also use the function with other datetime values, such as TIMESTAMP, etc.

The TO_CHAR(datetime) Function

We can also use the TO_CHAR(datetime) function as an alternative method to get the day from a date.

This function accepts the datetime or interval value as its first argument, and a format model as its second argument. The function then converts the value to a data type of VARCHAR2 in the specified format.

The format model specifies the format for which to return the datetime/interval value. The format model consists of one or more format elements. This enables us to carefully craft the results to reflect our desired format.

If we only want to return the day, we can use one of the format elements for extracting the day.

Day of Month

To get the day of the month (between 1-31), use the DD format element:

SELECT TO_CHAR(DATE '2037-10-03', 'DD')
FROM DUAL;

Result:

03

Full Day Name

To get the full day name, use DAY:

SELECT TO_CHAR(DATE '2037-10-03', 'DAY')
FROM DUAL;

Result:

SATURDAY

Short Day Name

To get the abbreviated day name, use DY:

SELECT TO_CHAR(DATE '2037-10-03', 'DY')
FROM DUAL;

Result:

SAT

Day of Year

To get the day of the year (between 1-366), use the DDD format element:

SELECT TO_CHAR(DATE '2037-10-03', 'DDD')
FROM DUAL;

Result:

276

Day of Week

To get the day of the week (between 1-7), use the D format element:

SELECT TO_CHAR(DATE '2037-10-03', 'D')
FROM DUAL;

Result:

6

This format element depends on the NLS territory of the session. The value of my system’s NLS_TERRITORY parameter is AUSTRALIA, and therefore, Saturday is considered to be the sixth day of the week.

Here’s what happens if I change the value of my NLS_TERRITORY parameter to AMERICA and run the same query again:

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
SELECT TO_CHAR(DATE '2037-10-03', 'D')
FROM DUAL;

Result:

7

This time Saturday is considered to be the seventh day of the week.