How to use the Date_Part() Function In SQL

In PostgreSQL, A useful tool for removing particular elements from a date or timestamp is the DATE_PART function. Particularly helpful are the ‘dow‘ and ‘isodow‘ parameters for getting the day of the week.

1) Day Of Week (Sunday to Saturday, 0 to 6):

Syntax:

SELECT DATE_PART('dow', column_name) AS day_of_week
FROM your_table;

Replace your_table with the name of the actual table and column_name with the name of the actual column that contains the date field. The outcome will be an integer with Sunday being 0 and Saturday being 6, signifying the day of the week.

Example: Extracting Day of Week using DATE_PART in PostgreSQL

SELECT sample_dates,DATE_PART('dow', sample_dates) AS day_of_week
FROM example_table;

Output:

Explanation: The given SQL statement creates a new column called day_of_week and pulls the sample_dates column from the example_table. For each date in the given table, the DATE_PART function is used to extract the day of the week as an integer (0 for Sunday, 1 for Monday, and so on). The outcome shows the original dates next to the day of the week represented by a number.

2) ISO Day Of Week (Monday to Sunday, 1 to 7):

Syntax:

SELECT DATE_PART('isodow', column_name) AS day_of_week
FROM your_table;

Replace your_table with the name of the actual table and column_name with the name of the actual column that contains the date field. The outcome will be an integer with Monday being 1 and Sunday being 7, signifying the day of the week.

Example: Retrieving the ISO Day of the Week Using DATE_PART Function in PostgreSQL

SELECT sample_dates,DATE_PART('isodow', sample_dates) AS day_of_week
FROM example_table;

Output:

Explanation: This SQL statement creates a new column called day_of_week by using the DATE_PART function with the ‘isodow’ argument. It also picks the sample_dates column from the example_table. It provides a numerical depiction of weekdays by extracting the ISO day of the week (which ranges from 1 for Monday to 7 for Sunday) for every date in the table.

In PostgreSQL, extracting the day of the week from a date field is a common and essential task when working with temporal data. Knowing the day of the week is crucial for various applications, including scheduling, reporting, and analytical purposes. PostgreSQL provides multiple methods to achieve this, offering flexibility based on specific requirements.

This article explores three common techniques: using the EXTRACT function, the TO_CHAR function, and the DATE_PART function, each catering to different scenarios for extracting day-of-week information from date fields in PostgreSQL.