How to Get the Day of the Week for PL/SQL?

PL/SQL is a Procedural language extension for the Structured Query Language. SQL is a standard database language that can be used to manipulate and access relational databases where data is stored in tables. This language can create tables and manipulate the data stored in the tables using queries or statements like insert, delete, update, alter, etc.

The day of the week in PL/SQL can be used for a variety of database operations. In this article, we’ll look at how to retrieve this data in PL/SQL. We will start with the basics, like the TO_CHAR function and EXTRACT function.

With clear explanations and examples, you’ll learn how to use these techniques to improve your PL/SQL projects, improve database management, and make data analysis easier.

How to Retrieve the Day of the Week Using PL/SQL

Retrieving the day of the week from a given date is frequently encountered in database programming. In PL/SQL, there are multiple approaches to tackle this problem effectively.

• TO_CHAR function
• EXTRACT function

1. TO_CHAR function

The TO_CHAR function is a versatile tool in PL/SQL used for converting values to varchar datatype with various formatting options.

Syntax:

TO_CHAR(date_value, format_mask);

Example 1: Retrieving Day of the Week Using TO_CHAR Function

DECLARE
    today_date DATE:= SYSDATE; 
    day_of_week VARCHAR2(10);
BEGIN
   -- DY returns the first 3 characters 
  -- DAY format returns the full name of day of week
    day_of_week := TO_CHAR(today_date, 'DY');
    DBMS_OUTPUT.PUT_LINE('Day of the week: ' || day_of_week);
END;

Here, in the above code, I declared two variables where one variable is the date and another variable is for storing the day of week value after processing.

TO_CHAR functions take two arguments where one argument is the above context is the date and the format that needs to be changed is the second argument.

Output:

TO_CHAR function DY format

Explanation: In this example, the TO_CHAR function is utilized to convert the current date into a string representing the abbreviated day of the week (‘DY’ format). The result is then displayed using the DBMS_OUTPUT.PUT_LINE function.

Example 2: Retrieving Day of the Week Using TO_CHAR Function

DECLARE
    today_date DATE:= SYSDATE; 
    day_of_week VARCHAR2(10);
BEGIN
   -- DY returns the first 3 characters 
  -- DAY format returns the full name of day of week
    day_of_week := TO_CHAR(today_date, 'DY');
    DBMS_OUTPUT.PUT_LINE('Day of the week: ' || day_of_week);
END;

Output 2:

TO_CHAR function DAY format

Explanation: In the above outputs, we can observe that the DBMS_OUTPUT.PUT_LINE statement is used for returning the result and the ‘DY’ format returns the first 3 characters of the day of week string and the ‘DAY‘ format returns the full name of the day of week.

EXTRACT Function

This function is used to retrieve or extract specific components from the date. We can extract day of the week from a date and the function returns an integer or numeric value. This numeric value describes the day of the week ( 1 – Sunday,2 – Monday, and so on).

Syntax:

EXTRACT(WEEKDAY FROM date_value)

Example: Retrieving Day of the Week Using EXTRACT Function

DECLARE
    my_date DATE:= SYSDATE; 
    day_of_week NUMBER;
BEGIN
    day_of_week := EXTRACT(DAY FROM my_date);
    DBMS_OUTPUT.PUT_LINE('Day of the week(numeric): ' || day_of_week);
    DBMS_OUTPUT.PUT_LINE('Day of the week(full name): ' || TO_CHAR(my_date, 'DY'));
END;

Output:

Extract Function

Explanation: The code retrieves the day of the week from the current date using the EXTRACT function and assigns it to the variable “day_of_week.” It then prints the numeric representation of the day and its full name using the DBMS_OUTPUT.PUT_LINE function. This provides both numeric and textual representations of the day of the week.

Conclusion

PL/SQL, as a procedural language extension for SQL, offers powerful tools and functions for database manipulation tasks. By mastering techniques such as the TO_CHAR and EXTRACT functions, developers can efficiently retrieve the day of the week from dates within their PL/SQL programs. This not only enhances the functionality of database applications but also contributes to better data management and analysis.