BBampaa

Lessons › SQL › Chapter 8: Text, dates and safe maths

SQL lesson

Month and weekday names with TO_CHAR

TO_CHAR turns a date into text in the shape you choose. Mon gives Jan, Feb…, Dy gives Mon, Tue…, and YYYY-MM gives 2026-03. It's handy for report labels and for grouping sales by weekday or month.

Try this lesson free →
About 5–10 minutes · no sign-up needed

Example

Dates as words

SELECT order_id, TO_CHAR(order_date, 'Dy') AS weekday
FROM orders

Result:

order_idweekday
O2001Sun
O2002Thu
O2003Wed
O2004Wed
O2005Fri
O2006Fri

Step by step

  1. The short weekday name: Mon, Tue…
    SELECT order_id, TO_CHAR(order_date, 'Dy') AS weekday FROM orders
  2. The short month name: Jan, Feb…
    SELECT order_id, TO_CHAR(order_date, 'Mon') AS month_name FROM orders
  3. A month label like 2026-03. It sorts in date order.
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'YYYY-MM') ORDER BY month
  4. 'Day' and 'Month' give full names padded with spaces, so 'Saturday ' won't equal 'Saturday'. FM in front drops the spaces: 'FMDay', 'FMMonth'.
    SELECT order_id, TO_CHAR(order_date, 'FMDay') AS weekday FROM orders

Practice

Try each one in your head first, then open the answer.

1. Using orders, show order_id and the weekday each order was placed, like Mon. Name it weekday.
SELECT order_id, TO_CHAR(order_date, 'Dy') AS weekday FROM orders
2. Using orders, count the orders on each weekday. Show weekday (like Mon) and orders.
SELECT TO_CHAR(order_date, 'Dy') AS weekday, COUNT(*) AS orders FROM orders GROUP BY TO_CHAR(order_date, 'Dy')
3. Using orders, show total revenue for each month, labelled like 2026-03, in month order. Show month and revenue.
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(revenue) AS revenue FROM orders GROUP BY TO_CHAR(order_date, 'YYYY-MM') ORDER BY month
4. Using orders, show order_id and the full weekday name, like Saturday. Name it weekday.
SELECT order_id, TO_CHAR(order_date, 'FMDay') AS weekday FROM orders
5. Using orders, show order_id and the short month name each order was placed in, like Mar. Name it month_name.
SELECT order_id, TO_CHAR(order_date, 'Mon') AS month_name FROM orders

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →
About 5–10 minutes · no sign-up needed