SQL filtering by date

Learn SQL with Metabase

Download Metabase for free, or sign up for a free trial of Metabase Cloud

People like to know when things happened. This guide shows you how to filter your data by dates, from simple exact matches to complex patterns like business days and rolling periods.

What we’ll cover

• The difference between dates and timestamps
• Exact date matches
• Before or after a date
• Date ranges with BETWEEN
• Part of a date (week, month, etc.)
• Relative dates
• Day of week
• Range of hours
• Fiscal periods
• Recurring dates
• Business days
• Last X days
• Difference between two dates
• Date ranges with gaps
• Missing dates
• Date variables

Difference between dates and timestamps and dates stored as strings

In SQL, DATE and TIMESTAMP are distinct data types:

SELECT
  DATE '2025-05-04' AS this_is_a_date,
  TIMESTAMP '2025-05-04 14:30:00' AS this_is_a_timestamp
FROM
  orders
LIMIT
  1;

A DATE value:

• Stores only the calendar date (year, month, day)
• Lacks a time component
• Has a typical format of ‘YYYY-MM-DD’
• Takes (slightly) less storage space

A TIMESTAMP value:

• Stores both date and time (year, month, day, hour, minute, second, and often fractional seconds)
• Has a typical format of YYYY-MM-DD HH:MM:SS.SSS (the fractional milliseconds, .SSS, are optional)
• May include time zone information (TIMESTAMP WITH TIME ZONE or TIMESTAMPTZ)

Dates can also (rarely) be stored as strings (that is, text).

Most tools (including Metabase) give you a column’s type information in a data reference section. You can also typically query the database’s INFORMATION_SCHEMA. Here’s how you’d get the data type for the columns in the orders table of the Sample Database:

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'ORDERS';

Which returns:

| TABLE_NAME | COLUMN_NAME | DATA_TYPE        |
| ---------- | ----------- | ---------------- |
| ORDERS     | ID          | BIGINT           |
| ORDERS     | USER_ID     | INTEGER          |
| ORDERS     | PRODUCT_ID  | INTEGER          |
| ORDERS     | SUBTOTAL    | DOUBLE PRECISION |
| ORDERS     | TAX         | DOUBLE PRECISION |
| ORDERS     | TOTAL       | DOUBLE PRECISION |
| ORDERS     | DISCOUNT    | DOUBLE PRECISION |
| ORDERS     | CREATED_AT  | TIMESTAMP        |
| ORDERS     | QUANTITY    | INTEGER          |

Here the date column, CREATED_AT is a TIMESTAMP.

In practice, unless you’re working with date where exact times matter, you’re going to want to convert to the DATE type when querying tables, as you’ll typically be filtering and grouping results by days (or weeks, months, quarters, or years).

Convert a timestamp to a date

You can use CAST to convert a timestamp to a date:

SELECT
  id,
  CAST(created_at AS DATE) AS order_date
FROM
  orders;

SQL to filter rows by a single date

To search for an exact date match, use the WHERE clause with the = operator. Here’s a query to get all the orders placed on May 4th, 2025.

SELECT
  id,
  created_at
FROM
  orders
WHERE
  created_at >= DATE '2025-05-04'
  AND created_at < DATE '2025-05-05';

Why not just WHERE created_at = '2025-05-04'? Two reasons:

• created_at is a field with timestamps. So even though WHERE created_at = '2025-05-04' is a valid clause, that filter would only return orders placed at 2025-05-04T00:00:00 (midnight on May 4th, 2025). Using AND we can ask the database to return all orders placed between midnight on May 4th up to (but not including) midnight on May 5th.
• Using a range keeps the query sargable, which is jargon for “queries that allow the query processor to take advantage of any indexes on a column”. Sargable is short for Search ARGument ABLE. (We’ll cover indexes in another article.)

Alternatively, you could convert the timestamp to a date, like so:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Converting the column to a date type to lop off the time
  CAST(created_at AS DATE) = DATE '2025-05-04';

This query works, but because the query processor has to run a CAST function on each value in the column, the query processor can’t take advantage of any index on the column to speed up results (which is to say: the query isn’t sargable).

The DATE keyword isn’t necessary. Most database will recognize YYYY-MM-DD as a date, but you might as well be explicit.

SQL to filter before or after a date

You can use comparison operators to find dates before or after a specific date. Here we’re getting orders from BEFORE May 4th, 2025.

SELECT
  *
FROM
  orders
WHERE
  -- Get orders from before midnight on May 4th, 2025
  -- (midnight is the start of a day)
  created_at < DATE '2025-05-04';

If you want to include orders placed during the day on 2025-05-04, you could either bump the date up to 2025-05-05, or use INTERVAL to add a day:

SELECT
  *
FROM
  orders
WHERE
  -- Get orders from May 4th, 2025 and before
  created_at < DATE '2025-05-04' + INTERVAL '1' DAY;

SQL supports all the standard comparison operators, but keep in mind that these operators will return different results depending on whether you’re working with dates or timestamps.

• > (after)
• >= (on or after)
• < (before)
• <= (on or before: if you’re working with timestamps, you’ll only include midnight for that date).

SQL to filter date ranges with BETWEEN

To find dates within a range, use BETWEEN. Here we’re filtering for orders placed between midnight May 1 and midnight May 15, 2025.

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders from midnight May 1 through midnight May 15, 2025
  created_at BETWEEN DATE '2025-05-01' AND  DATE '2025-05-15';

Even though BETWEEN is inclusive of both the start and end dates, this query won’t return all orders placed on 2025-05-15. That’s because the created_at column includes timestamps, not dates, so the query will only include orders placed up to midnight May 15. If you want to include orders placed at other times on the 15th, you’ll need to bump the range up to the 16th.

Alternatively, you could compose comparative filters to return a range. Here’s a translation of the query above with BETWEEN: we’re again filtering for orders placed between midnight May 1 and midnight May 15, 2025.

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Mimics BETWEEN: gets orders from
  -- midnight May 1 through midnight May 15, 2025
  -- If you wanted to include all orders on the 15th,
  -- you'd need to write `< '2025-05-16'`
  created_at >= DATE '2025-05-01'
  AND created_at <= DATE '2025-05-15';

SQL to filter by part of a date (by week or month and so on)

You can filter based on specific parts of a date (like year, month, or day) using EXTRACT. Let’s say you want to get all the orders placed in May, regardless of the year. You can extract the MONTH FROM a date column, like so:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get all orders created in May
  EXTRACT(MONTH FROM created_at) = 5;

You can also extract:

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND
• DOW (day of week)
• DOY (day of year)

SQL to filter by relative dates

To filter by relative dates, like the previous X number of days, you can use CURRENT_DATE and INTERVAL. Here’s a query to get orders placed in the last seven days (including today):

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Since we're working with timestamps, CURRENT_DATE will return the current date at midnight
  -- So we'll need to add a day to include orders placed on the current date.
  created_at <= CURRENT_DATE + INTERVAL '1' DAY
  -- Get orders from the last 7 days
  AND created_at >= CURRENT_DATE - INTERVAL '7' DAY;

Relative date functions differ from database to database, so you’ll need to look up which ones your database uses. Common relative date function names include:

• CURRENT_DATE: today’s date
• CURRENT_TIMESTAMP: current date and time
• NOW(): current date and time
• INTERVAL: specify a time period

The INTERVAL keyword accepts various time units. Here are the most commonly supported units:

• YEAR / YEARS
• MONTH / MONTHS
• WEEK / WEEKS
• DAY / DAYS
• HOUR / HOURS
• MINUTE / MINUTES
• SECOND / SECONDS
• MILLISECOND / MILLISECONDS

Note that different databases may support different units or have slightly different syntax. Always check your database’s documentation for the complete list of supported interval units.

SQL to filter by day of week

To find orders placed on specific days of the week, you can EXTRACT the DOW (day of week). Here’s a query to get all the orders placed on either Monday or Friday.

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders placed on Mondays (2) and Fridays (6)
  EXTRACT(DOW FROM created_at) IN (2, 6);

Inconveniently, different databases number the days of week differently, so check your query results to make sure the number is returning the correct day of the week.

SQL to filter by range of hours

To find orders placed during a range of hours, regardless of the date, we can EXTRACT the hours BETWEEN two hours of the day. Here we’re filtering orders placed between 09:00 and 17:59 each day:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders placed between 9 AM and 5 PM
  EXTRACT(HOUR FROM created_at) BETWEEN 9 AND 17;

Note that the hour includes the entire hour. If you wanted to cut off orders placed after 5 PM (17:00), you’d need to use BETWEEN 9 AND 16.

SQL to filter by fiscal periods

To find orders from specific fiscal periods (like quarters or fiscal years), you can EXTRACT the QUARTER and the YEAR. Here’s a query that gets all orders from Q2 of 2025:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders from Q2 2025 (April through June)
  EXTRACT(QUARTER FROM created_at) = 2
  AND
  EXTRACT(YEAR FROM created_at) = 2025;

SQL to filter by recurring dates

To find orders that occur on the same day each month, use EXTRACT and the = operator. Here’s a query that finds all the orders placed on the 15th of each month:

SELECT
  *
FROM
  orders
WHERE
  -- Get orders placed on the 15th of any month
  EXTRACT(DAY FROM created_at) = 15;

Obviously, some months have fewer days than others. If you’re looking for the 31st of each month, you’ll miss February, April, June, September, and November. To grab all the orders placed on the last day of every month, you can use EXTRACT and INTERVAL:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Orders placed on the last day of the each month
  EXTRACT(DAY FROM (created_at + INTERVAL '1' DAY)) = 1;

EXTRACT(DAY FROM (created_at + INTERVAL '1' DAY)) = 1 checks if adding one day to the value of created_at makes the day of the month the 1st. To break it down further:

• created_at + INTERVAL '1' DAY is the date + one day.
• EXTRACT (DAY FROM ...) gets the day’s value.
• Which we compare with the value 1 (i.e., if we add one to day, is it the first day of the next month?)

If adding a day to created_at’s value equals 1 (i.e., the first day of the month), that means the created_at value must be the last day of the previous month.

SQL to filter by business days

To find orders placed on business days, Monday through Friday, (excluding weekends), you can EXTRACT the day of the week and filter for a range with BETWEEN. Here’s a query to filter for orders placed Monday through Friday.

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- If your Day of Week (DOW)starts Sunday as 1, then it's BETWEEN 2 AND 6 (Mon-Fri)
  -- If your DOW starts Monday as 1, then it's BETWEEN 1 AND 5 (Mon-Fri)
 EXTRACT(DOW FROM created_at) BETWEEN 2 AND 6;

You can additionally specify a set of holidays to exclude with NOT IN:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  created_at > '2024-12-31'
  AND created_at < '2025-02-01'
  AND EXTRACT(DOW FROM created_at) BETWEEN 2 AND 6
  -- Exclude some American holidays in 2025.
  -- Because created_at is a timestamp, we'll need to cast it as a date.
  AND CAST(created_at AS DATE) NOT IN (
    DATE '2025-01-01', -- New Year's Day
    DATE '2025-07-04', -- Independence Day
    DATE '2025-09-01', -- Labor Day
    DATE '2025-09-07', -- Yet Another Syntax Error Day
    DATE '2025-11-27' -- Thanksgiving Day
    -- and whatever other holidays and dates you want to exclude
  );

If we hadn’t cast created_at to a date, we would’ve only excluded orders placed at exactly midnight on those days.

SQL to filter for last X days

To find orders based on how old they are, you can use BETWEEN, CURRENT_DATE, and INTERVAL. Here’s a query that filters for orders placed between 30 and 60 days ago.

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders that are between 30 and 60 days old
  created_at BETWEEN CURRENT_DATE - INTERVAL '60' DAY AND CURRENT_DATE - INTERVAL '30' DAY;

SQL to filter by the difference between two dates

Let’s say we want to see all the accounts that were canceled within three days from their creation. We’ll need to:

• Filter for canceled accounts
• Extract the day from the two date columns so we can calculate the difference
• Filter for differences less than or equal to 3.

SELECT
  id,
  created_at,
  canceled_at,
  -- Get the day number for the date
  -- Calculate the difference in dates
  EXTRACT(DAY FROM canceled_at - created_at) AS days_active
FROM
  accounts
WHERE
  -- Filter for canceled accounts.
  canceled_at IS NOT NULL
 AND
  -- Calculate the day again
  EXTRACT(DAY FROM canceled_at - created_at) <= 3;

The reason you can’t use the days_active alias in the WHERE clause is due to the order of operations in SQL. In this case, the WHERE clause is evaluated before the SELECT clause, so it’s not aware of the days_active alias. Though, of course, db engines differ, so you may be able to refer to the alias in some cases. If you want to avoid writing out EXTRACT twice, you could use a common table expression:

WITH account_activity AS (
  SELECT
    id,
    created_at,
    canceled_at,
    EXTRACT(DAY FROM canceled_at - created_at) AS days_active
  FROM
    accounts
  WHERE
    canceled_at IS NOT NULL
)

SELECT
  *
FROM
  account_activity
WHERE
  days_active <= 3;

While not standard SQL, many databases support a DATEDIFF function that lets you filter rows based on the difference between dates measured in specific units. You can use DATEDIFF to find records where a specific amount of time has passed between two dates.

SELECT
  id,
  created_at,
  canceled_at,
  DATEDIFF (DAY, created_at, canceled_at) as days_active
FROM
  accounts
WHERE
  -- Filter for canceled accounts
  canceled_at IS NOT NULL
  -- And filter for accounts that canceled within three days
  AND DATEDIFF (DAY, created_at, canceled_at) <= 3;

Here DATEDIFF takes a unit (DAY), and a start and end date, and calculates the number of units (in this case days) between the two dates. Some databases implement the DATEDIFF function differently, so check their docs for their function signature.

SQL to filter by date ranges with gaps

To find orders in specific date ranges while excluding certain periods, you can combine multiple filters with AND. Here we’re getting dates in May 2025, except for weekends, and any orders created during the big, hypothetical sale we ran on May 25th and 26th:

SELECT
  id,
  created_at
FROM
  orders
WHERE
  -- Get orders from May 2025
  created_at >= '2025-05-01'
  AND created_at < '2025-06-01'
  -- But exclude weekends: not Sunday (1) or Saturday (7)
  -- Though note that the numbering of days of the week isn't consistent across databases
  AND EXTRACT(DOW FROM created_at) NOT IN (1, 7)
  -- And exclude specific dates
  -- Which we'll need to cast, as created_at contains timestamps
  AND CAST(created_at AS DATE) NOT IN (
    DATE '2025-05-25',
    DATE '2025-05-26'
    );

SQL to filter for, or exclude, rows with missing dates

To find rows where a date column is empty (null):

SELECT
  *
FROM
  orders
WHERE
  created_at IS NULL;

To find rows that actually have a value (are not null):

SELECT
  *
FROM
  orders
WHERE
  created_at IS NOT NULL;

SQL to filter by a date variable

Check out SQL parameters.