BBampaa

Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Cleaning date formats with TO_DATE() and TO_CHAR()

TO_DATE() converts a date stored as plain text into a real date the database can sort, filter, and do math on.

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

Example

Cleaning Retail Date Formats with TO_DATE() and TO_CHAR()

SELECT order_id,
       TO_DATE(raw_order_date, 'DD-Mon-YYYY') AS clean_order_date,
       TO_CHAR(TO_DATE(raw_order_date, 'DD-Mon-YYYY'), 'YYYY-MM-DD') AS iso_order_date
FROM orders
WHERE order_id = 'O2001'

Result:

order_idclean_order_dateiso_order_date
O20012024-01-152024-01-15

Step by step

  1. AS gives a column a friendlier name in the result.
    SELECT order_id, TO_DATE(raw_order_date, 'DD-Mon-YYYY') AS clean_order_date, TO_CHAR(TO_DATE(raw_order_date, 'DD-Mon-YYYY'), 'YYYY-MM-DD') AS iso_order_date
  2. FROM tells SQL which table to pull the data from.
    SELECT order_id, TO_DATE(raw_order_date, 'DD-Mon-YYYY') AS clean_order_date, TO_CHAR(TO_DATE(raw_order_date, 'DD-Mon-YYYY'), 'YYYY-MM-DD') AS iso_order_date
FROM orders
  3. WHERE keeps only the rows that match your condition.
    SELECT order_id, TO_DATE(raw_order_date, 'DD-Mon-YYYY') AS clean_order_date, TO_CHAR(TO_DATE(raw_order_date, 'DD-Mon-YYYY'), 'YYYY-MM-DD') AS iso_order_date
FROM orders
WHERE order_id = 'O2001';

Practice

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

1. In orders_raw, order_date_text stores dates as text like '01/03/2024' (DD/MM/YYYY). Convert it to a real date called order_date with TO_DATE.
SELECT order_id, TO_DATE(order_date_text, 'DD/MM/YYYY') AS order_date FROM orders_raw
2. In customers_raw, signup_text stores dates like '2024/01/15'. Convert it to a real date called signup_date.
SELECT customer_id, TO_DATE(signup_text, 'YYYY/MM/DD') AS signup_date FROM customers_raw
3. In store_sales_raw, sale_text stores dates like '01-03-2024' (DD-MM-YYYY). Convert it to a real date called sale_date.
SELECT sale_id, TO_DATE(sale_text, 'DD-MM-YYYY') AS sale_date FROM store_sales_raw
4. Using orders, show order_id and order_date formatted as 'DD/MM/YYYY' text, named order_date_text.
SELECT order_id, TO_CHAR(order_date, 'DD/MM/YYYY') AS order_date_text FROM orders
5. Using shipments, show shipment_id and ship_date formatted as 'YYYY-MM' text, named ship_month.
SELECT shipment_id, TO_CHAR(ship_date, 'YYYY-MM') AS ship_month FROM shipments

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