EXTRACT DATE

EXTRACT pulls one piece out of a full date - the year, month, day, or other part - and returns it as a plain number you can sort, filter, or group by.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Extracting Date Parts from Retail Orders

SELECT order_id, order_date, EXTRACT(MONTH FROM order_date) AS order_month
FROM orders

Result:

order_id	order_date	order_month
O2001	2024-03-14	3
O2002	2024-03-22	3
O2003	2024-07-05	7
O2004	2024-07-19	7
O2005	2024-11-30	11

Step by step

Run the query without EXTRACT so you can see what the unfiltered values look like.
```
SELECT order_id, order_date, MONTH
FROM orders;
```

EXTRACT pulls a part of a date or timestamp - YEAR, MONTH, DAY, etc.

SELECT order_id, order_date, EXTRACT(MONTH FROM order_date)
FROM orders;

AS gives the EXTRACT result a friendly column name so the output reads cleanly.

SELECT order_id, order_date, EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Practice

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

1. Using orders, write a query to extract just the month from each order_date.

SELECT EXTRACT(MONTH FROM order_date) FROM orders

2. Using shipments, write a query to extract just the month from each ship_date.

SELECT EXTRACT(MONTH FROM ship_date) FROM shipments

3. Using promotions, write a query to extract just the month from each start_date.

SELECT EXTRACT(MONTH FROM start_date) FROM promotions

4. Using store_sales, write a query to extract just the day from each sale_date.

SELECT EXTRACT(DAY FROM sale_date) FROM store_sales

5. Using returns, write a query to extract just the day from each return_date.

SELECT EXTRACT(DAY FROM return_date) FROM returns

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