Date conversion basics

CAST(column AS DATE) keeps just the day from a date-and-time, so orders placed at 9am and 5pm on the same day group together.

About 5–10 minutes · no sign-up needed

Example

Turning timestamps into dates

SELECT order_id, CAST(ordered_at AS DATE) AS order_day,
       ship_date - CAST(ordered_at AS DATE) AS days_to_ship
FROM orders

Result:

ordered_at has a date and a time.

SELECT order_id, ordered_at
FROM orders;

CAST(... AS DATE) drops the time.

SELECT order_id, CAST(ordered_at AS DATE) AS order_day
FROM orders;

Date minus date gives a number of days.

SELECT order_id, CAST(ordered_at AS DATE) AS order_day,
       ship_date - CAST(ordered_at AS DATE) AS days_to_ship
FROM orders;

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

1. Show order_id and the day each order was placed, as order_day.

SELECT order_id, CAST(ordered_at AS DATE) AS order_day FROM orders

2. Show order_id and how many days each order took to ship, as days_to_ship.

SELECT order_id, ship_date - CAST(ordered_at AS DATE) AS days_to_ship FROM orders

3. Show each day's total revenue: the order day as order_day, and SUM(revenue) as total.

SELECT CAST(ordered_at AS DATE) AS order_day, SUM(revenue) AS total FROM orders GROUP BY CAST(ordered_at AS DATE)

4. Show order_id for orders placed on '2024-03-01'.

SELECT order_id FROM orders WHERE CAST(ordered_at AS DATE) = '2024-03-01'

5. Show order_id, order_day and days_to_ship for every order.

SELECT order_id, CAST(ordered_at AS DATE) AS order_day, ship_date - CAST(ordered_at AS DATE) AS days_to_ship 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.

About 5–10 minutes · no sign-up needed