How to use the TO_CHAR() Function In SQL

In PostgreSQL, you can format a date or timestamp as a string by using the TO_CHAR function. It can be utilized to extract the day of the week in a textual representation.

Syntax:

SELECT TO_CHAR(column_name, Format Specifier) 
FROM table_name;

Here replace table_name with the name of the table and column_name with name of the column that has date data type.

Some Format Specifiers can be the following :

1. ‘Day’: Full name of the day of the week (e.g. “Tuesday”).

2. ‘day’: Full name of the day of the week in lowercase(e.g. “Tuesday”).

3. ‘DAY’: Full name of the day of the week in UPPERCASE(e.g. “TUESDAY”).

4. ‘Dy’: Three-letter abbreviation of the day of the week (e.g. “Tue”).

5. ‘dy‘: Three-letter abbreviation of the day of the week in lowercase (e.g. “true”).

6. ‘DY’: Three-letter abbreviation of the day of the week in UPPERCASE(e.g. “TUE”).

Example: Displaying Day of Week as Text from Date Field in PostgreSQL

SELECT sample_dates,TO_CHAR(sample_dates, 'Day') AS day_of_week
FROM example_table;

Output:

Explanation: The provided SQL query creates a new column called day_of_week and pulls the sample_dates column from the example_table. The date data in sample_dates are formatted into their corresponding complete day names using the TO_CHAR function. The output shows a list of dates combined with the matching day of the week, presenting a legible picture of the weekdays that correspond to each date in the given data.

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.