How to Query Date and Time

SQL Tutorial Home

If you want to obtain data from any specific date, just put the date in the format ‘yyyy-mm-dd’. If the column data type is timestamp, you can cast to date using the cast() function.

WITH sales AS (
  SELECT DATETIME '2022-04-03 11:06:00' AS sold_at,20.34 as revenue UNION ALL
  SELECT DATETIME '2022-04-03 10:25:00', 10.00 UNION ALL
  SELECT DATETIME '2022-04-04 11:55:00', 16.00 UNION ALL
  SELECT DATETIME '2022-04-03 11:34:00', 10.06
)
select * from sales where cast(sold_at as date) = '2022-04-04';

If you want to obtain the sales done on each day in a specific period of time, for example between 11:00 am and 12:00 am, you can cast the timestamp to time and filter in a range of time. Then we can group by date.

WITH sales AS (
  SELECT DATETIME '2022-04-03 11:06:00' AS sold_at,20.34 as revenue UNION ALL
  SELECT DATETIME '2022-04-03 10:25:00', 10.00 UNION ALL
  SELECT DATETIME '2022-04-04 11:55:00', 16.00 UNION ALL
  SELECT DATETIME '2022-04-03 11:34:00', 10.06
)
select cast(sold_at as date) as day, sum(revenue) as revenue 
from sales
where cast(sold_at as time) between '11:00:00' AND '12:00:00'
group by 1 order by 1;
day revenue
2022-04-03 30.40
2022-04-04 16.00

IN THIS PAGE