SQL Quiz: Date Range Filtering
10 questions · exam conditions
0:00
Date Range FilteringQuestion 1 of 10

Reservations are represented as half-open intervals: start_ts is included and end_ts is excluded. A report window is also half-open, from :window_start through, but not including, :window_end.

Which condition returns every reservation that overlaps the report window by a nonzero amount of time?

start_ts >= :window_start AND end_ts <= :window_end
start_ts <= :window_end AND end_ts >= :window_start
start_ts < :window_end AND end_ts > :window_start
start_ts < :window_start OR end_ts > :window_end
← Back to quizzes

SQL Quiz

SQL Quiz: Date Range Filtering

Practice Date Range Filtering in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Date Range Filtering, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

Reservations are represented as half-open intervals: start_ts is included and end_ts is excluded. A report window is also half-open, from :window_start through, but not including, :window_end.

Which condition returns every reservation that overlaps the report window by a nonzero amount of time?

  1. start_ts >= :window_start AND end_ts <= :window_end
  2. start_ts <= :window_end AND end_ts >= :window_start
  3. start_ts < :window_end AND end_ts > :window_start (correct answer)
  4. start_ts < :window_start OR end_ts > :window_end
Explanation: Interval overlap is one of the trickiest conditions to write correctly in SQL. The key insight is to think about when two intervals do NOT overlap, then negate it — this is often easier than reasoning about overlap directly. Two half-open intervals [A_start, A_end) and [B_start, B_end) are completely non-overlapping when one ends before the other begins. Specifically, the reservation ends at or before the window starts (end_ts <= :window_start), or the reservation starts at or after the window ends (start_ts >= :window_end). Negate that entire condition using De Morgan's Law, and you get the overlap condition: end_ts > :window_start AND start_ts < :window_end — which is exactly answer C. The strict inequalities (< and >) are critical here because the intervals are half-open; touching at a single boundary point means zero shared time, which the question explicitly excludes. Answer A is too restrictive — it only captures reservations fully contained within the window, missing reservations that extend beyond either boundary. Answer B uses <= and >= instead of strict inequalities, which incorrectly includes cases where boundaries merely touch (e.g., a reservation ending exactly at :window_start), producing zero-duration "overlap." Answer D is a disjunction testing whether the reservation extends outside the window, which catches many non-overlapping cases and is logically unrelated to the overlap requirement. The study tip to remember: derive overlap by negating non-overlap. Write out the two "miss" cases, flip them with De Morgan's Law, and you'll get the correct condition every time — and you'll know exactly which inequality sign to use.

Question 2

On July 9, 2026, a report must return events from the seven complete calendar dates immediately before the current date. Events from July 9 must be excluded. The system's CURRENT_DATE is DATE '2026-07-09'.

Which predicate selects exactly the requested dates when event_ts is a timestamp?

  1. event_ts >= DATE '2026-07-01' AND event_ts < DATE '2026-07-08'
  2. event_ts >= DATE '2026-07-02' AND event_ts <= DATE '2026-07-08'
  3. event_ts >= DATE '2026-07-02' AND event_ts < DATE '2026-07-09' (correct answer)
  4. event_ts >= DATE '2026-07-03' AND event_ts < DATE '2026-07-10'
Explanation: When filtering timestamps with a date range, the critical concept is half-open interval logic: use >= on the lower bound but < on the upper bound. This prevents accidentally cutting off the final microsecond of your intended last day, since a timestamp like 2026-07-08 23:59:59.999 is less than DATE '2026-07-09' (which implicitly means midnight of July 9) but would be excluded by <= DATE '2026-07-08'. Now count the seven complete calendar days before July 9: that's July 2, 3, 4, 5, 6, 7, and 8 — seven days exactly. So you need event_ts >= DATE '2026-07-02' (start of July 2) and event_ts < DATE '2026-07-09' (everything before the stroke of midnight on July 9, excluding July 9 itself). That's exactly what C expresses. A is wrong on both ends — the lower bound starts July 1 (eight days back, not seven) and the upper bound < DATE '2026-07-08' cuts off all of July 8, losing an entire required day. B uses <= DATE '2026-07-08' on a timestamp column, which only captures rows where event_ts is exactly 2026-07-08 00:00:00 — any timestamp later in July 8 is silently dropped. D shifts the entire window forward one day: it starts July 3 (only six of the seven required days) and extends into July 9, which the problem explicitly forbids. The takeaway: always pair a timestamp range with >= start_date AND < day_after_end. Never use <= with a date when your column holds timestamps.

Question 3

An audit report must include business events that occurred during January 2026, but only if their records had arrived strictly before the extraction cutoff 2026-02-05 18:00:00. The table stores business time in event_ts and arrival time in loaded_at.

Which predicate applies both timing requirements correctly?

  1. event_ts >= DATE '2026-01-01' AND event_ts < TIMESTAMP '2026-02-05 18:00:00' AND loaded_at < DATE '2026-02-01'
  2. loaded_at >= DATE '2026-01-01' AND loaded_at < DATE '2026-02-01' AND event_ts < TIMESTAMP '2026-02-05 18:00:00'
  3. event_ts >= DATE '2026-01-01' AND loaded_at < DATE '2026-02-01' AND loaded_at <= TIMESTAMP '2026-02-05 18:00:00'
  4. event_ts >= DATE '2026-01-01' AND event_ts < DATE '2026-02-01' AND loaded_at < TIMESTAMP '2026-02-05 18:00:00' (correct answer)
Explanation: When a question mixes two independent timing dimensions — when something happened versus when it arrived in the system — your first move should be to map each business requirement to the correct column before evaluating any predicate. Here, the requirements are: (1) the event must have occurred during January 2026, meaning event_ts >= '2026-01-01' AND event_ts < '2026-02-01'; and (2) the record must have arrived strictly before the extraction cutoff, meaning loaded_at < TIMESTAMP '2026-02-05 18:00:00'. Option D applies both conditions to the right columns — event_ts governs the business period, and loaded_at governs the arrival cutoff — making it the correct answer. Option A is tempting because it uses event_ts correctly for the start boundary, but it applies the extraction cutoff (2026-02-05 18:00:00) to event_ts instead of loaded_at, and then wrongly caps loaded_at at February 1st, which would exclude records that arrived between Feb 1 and the actual cutoff. Option B swaps the columns entirely — it filters loaded_at for the January business period and applies the cutoff to event_ts. This fundamentally confuses arrival time with business time. Option C starts correctly with event_ts >= '2026-01-01' but never closes the January upper bound on event_ts, meaning events from February or later could slip through. It also applies the cutoff logic redundantly and incorrectly to loaded_at. The key study habit: always label which column is business time and which is system/arrival time before reading the predicates. Mixing them is the most common trap in bi-temporal filtering questions.

Question 4

A price record is effective beginning at valid_from. It stops being effective at valid_to, with that endpoint excluded. A NULL valid_to means the price remains effective indefinitely.

Which condition finds the price effective at timestamp :as_of?

  1. valid_from < :as_of AND (valid_to >= :as_of OR valid_to IS NULL)
  2. valid_from <= :as_of AND (valid_to > :as_of OR valid_to IS NULL) (correct answer)
  3. valid_from <= :as_of AND valid_to > :as_of AND valid_to IS NOT NULL
  4. valid_from >= :as_of AND (valid_to < :as_of OR valid_to IS NULL)
Explanation: When working with temporal data in SQL, you need to think carefully about interval boundaries — specifically, whether each endpoint is inclusive or exclusive. The passage tells you the interval is closed on the left (valid_from is included) and open on the right (valid_to is excluded). In interval notation: [valid_from, valid_to). For a timestamp :as_of to fall inside this interval, you need valid_from <= :as_of (the start is inclusive, so equality counts) AND valid_to > :as_of (the end is exclusive, so equality means the price has already expired). When valid_to is NULL, the price never ends, so that row should always qualify — handled by the OR valid_to IS NULL clause. This is exactly what B expresses, making it correct. A is wrong because it uses valid_from < :as_of, which incorrectly excludes the moment when a price first becomes effective. If :as_of equals valid_from, the price is active, but A would miss it. C is wrong for two reasons: it excludes NULL valid_to rows entirely (dropping indefinitely-valid prices), and the condition valid_to IS NOT NULL combined with AND instead of OR breaks the open-ended case completely. D is wrong because the inequality is reversed — valid_from >= :as_of means you're finding prices that start after the query timestamp, which is the opposite of what you want. As a study tip, always identify whether each boundary is inclusive or exclusive before writing your condition — draw a quick number line if needed. Mixing up < vs <= on either side is the most common trap in temporal range queries.

Question 5

At 2026-07-09 12:00:00, an analyst runs a report for events in the immediately preceding 24-hour window. The event at the exact beginning of the window should be included, but an event at the exact report time should not be included.

Which predicate implements the requested window?

  1. event_ts >= TIMESTAMP '2026-07-08 00:00:00' AND event_ts < TIMESTAMP '2026-07-09 12:00:00'
  2. event_ts >= TIMESTAMP '2026-07-08 12:00:00' AND event_ts < TIMESTAMP '2026-07-09 12:00:00' (correct answer)
  3. event_ts > TIMESTAMP '2026-07-08 12:00:00' AND event_ts <= TIMESTAMP '2026-07-09 12:00:00'
  4. event_ts >= TIMESTAMP '2026-07-09 00:00:00' AND event_ts < TIMESTAMP '2026-07-10 00:00:00'
Explanation: When working with time-window predicates in SQL, you need to nail two things simultaneously: the boundary values (what timestamps define the window?) and the boundary logic (inclusive >=/<= or exclusive >/<?). The passage tells you the report runs at 2026-07-09 12:00:00 and you want the preceding 24 hours. Subtracting 24 hours gives a window start of 2026-07-08 12:00:00. The rules say the start is included and the report time itself is excluded, which means you need >= on the lower bound and < on the upper bound. That gives you exactly event_ts >= TIMESTAMP '2026-07-08 12:00:00' AND event_ts < TIMESTAMP '2026-07-09 12:00:00' — which is B. Choice A uses the right boundary logic (>= ... <) but gets the start timestamp wrong. 2026-07-08 00:00:00 is 36 hours before the report time, not 24, so the window is too wide. Choice C has the timestamps right but flips the boundary logic: the > excludes the window start (which the passage says should be included), and the <= includes the report time (which should be excluded). Choice D is completely off on both timestamps — it describes a future window from midnight on July 9 to midnight on July 10, which doesn't even overlap correctly with the report time. A reliable tip: always determine the window endpoints first (arithmetic), then apply boundary logic second (inclusive vs. exclusive). Mixing these two steps is exactly the trap that makes A, C, and D each wrong in a different way.

Question 6

A monitoring window repeats every day from 10:00 PM through, but not including, 2:00 AM. CAST(event_ts AS TIME) returns the event's local time of day.

Which predicate selects events in this overnight time window?

  1. CAST(event_ts AS TIME) >= TIME '22:00:00' AND CAST(event_ts AS TIME) < TIME '02:00:00'
  2. CAST(event_ts AS TIME) >= TIME '22:00:00' OR CAST(event_ts AS TIME) < TIME '02:00:00' (correct answer)
  3. CAST(event_ts AS TIME) > TIME '22:00:00' OR CAST(event_ts AS TIME) <= TIME '02:00:00'
  4. CAST(event_ts AS TIME) >= TIME '02:00:00' AND CAST(event_ts AS TIME) < TIME '22:00:00'
Explanation: When a time window crosses midnight, you can't express it as a single continuous range on a number line — the window "wraps around." This is the core trap this question is testing. Think about what the overnight window actually means: valid times are 10:00 PM onward (≥ 22:00) or before 2:00 AM (< 02:00). Because no single time value can simultaneously be both ≥ 22:00 and < 02:00 (those are on opposite sides of midnight), you need an OR to capture either end of the wrapped range. That makes B correct — it selects events that fall in the late-night portion (22:00–23:59) or the early-morning portion (00:00–01:59), which together form the complete overnight window. A fails because it uses AND, requiring a time to be both ≥ 22:00 and < 02:00 simultaneously. No time can satisfy that — the result set would always be empty. This is the classic midnight-wrap mistake. C uses OR but shifts the boundary operators incorrectly: it uses > instead of >= for 22:00 (excluding the window's exact start) and <= instead of < for 02:00 (including 02:00 itself, which the problem says should be excluded). D reverses the bounds entirely, selecting events between 02:00 AM and 10:00 PM — the opposite of the intended window. The study tip to remember: whenever a time range crosses midnight, AND becomes logically impossible for a single timestamp — switch to OR and test each boundary side separately.

Question 7

A database implicitly converts a DATE compared with a timestamp into a timestamp at midnight. The following predicate is applied to created_at: created_at BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'. Four rows have timestamps 2026-03-01 00:00:00, 2026-03-31 00:00:00, 2026-03-31 09:30:00, and 2026-04-01 00:00:00.

Which rows satisfy the predicate?

  1. Only the rows at March 1 midnight and March 31 midnight (correct answer)
  2. All three rows dated in March, but not the April 1 row
  3. Only the two March 31 rows, regardless of their times
  4. All four rows because BETWEEN expands both endpoint dates
Explanation: When a database implicitly converts a DATE to a timestamp, it sets the time component to midnight (00:00:00). This is the core concept being tested here. So when you write BETWEEN DATE '2026-03-01' AND DATE '2026-03-31', the database is actually evaluating BETWEEN TIMESTAMP '2026-03-01 00:00:00' AND TIMESTAMP '2026-03-31 00:00:00'. BETWEEN is inclusive on both endpoints, so the predicate matches any created_at value that is ≥ 2026-03-01 00:00:00 and ≤ 2026-03-31 00:00:00. Checking each row: 2026-03-01 00:00:00 equals the lower bound ✓, 2026-03-31 00:00:00 equals the upper bound ✓, 2026-03-31 09:30:00 exceeds the upper bound ✗, and 2026-04-01 00:00:00 also exceeds it ✗. That makes A the correct answer — only the two midnight rows qualify. Choice B is the most tempting trap. It assumes DATE '2026-03-31' covers the entire calendar day of March 31, but it doesn't — it only represents midnight of that day. The 9:30 AM row falls after the upper bound and is excluded. Choice C incorrectly focuses only on March 31 rows and ignores the March 1 midnight row, which clearly satisfies the lower bound. Choice D is simply false — BETWEEN does not magically expand date endpoints to cover full days; that behavior would require an explicit < DATE '2026-04-01' pattern instead. The key study tip: never assume a DATE endpoint in a BETWEEN clause covers the whole day. If you need to include all timestamps within a month, use created_at >= DATE '2026-03-01' AND created_at < DATE '2026-04-01' instead.

Question 8

A report runs on May 18, 2026 and must return transactions from the most recently completed calendar quarter. The transaction_ts column is a timestamp, and calendar quarters begin on January 1, April 1, July 1, and October 1.

Which predicate selects the intended quarter?

  1. transaction_ts >= DATE '2026-01-01' AND transaction_ts < DATE '2026-04-01' (correct answer)
  2. transaction_ts >= DATE '2026-02-18' AND transaction_ts < DATE '2026-05-18'
  3. transaction_ts >= DATE '2026-04-01' AND transaction_ts < DATE '2026-07-01'
  4. transaction_ts >= DATE '2025-10-01' AND transaction_ts < DATE '2026-01-01'
Explanation: When filtering by calendar quarter in SQL, you need to identify the most recently completed quarter relative to the report date — not the current or a rolling period. Since the report runs on May 18, 2026, and quarters close on January 1, April 1, July 1, and October 1, you should ask: which quarter has fully finished by this date? Q1 2026 (January–March) ended on March 31, making it the most recently completed quarter. Q2 2026 started April 1 but is still in progress. Answer A correctly captures Q1 2026 using >= DATE '2026-01-01' AND < DATE '2026-04-01'. The half-open interval pattern — inclusive lower bound, exclusive upper bound — is the right technique for timestamp ranges because it avoids accidentally including midnight of the end date while capturing every moment up to it. Answer B uses a rolling 90-day window anchored to May 18, which has nothing to do with calendar quarters. This is a common trap when you confuse "last 90 days" with "last quarter." Answer C selects Q2 2026 (April–June), which is the current, incomplete quarter as of May 18 — not the most recently completed one. Answer D selects Q4 2025 (October–December), which is two quarters back, not the most recent completed quarter. As a study tip, always sketch a calendar quarter timeline before writing your predicate. Identify the report date, mark which quarter is in progress, then step back one quarter — that's your target. The half-open interval (>= start AND < next_start) is the standard, reliable pattern for timestamp range filters.

Question 9

The orders.created_at column is a timestamp. A report must include every order created during March 2026, including orders at any time on March 31.

Which predicate most reliably filters the required rows without depending on the timestamp's fractional-second precision?

  1. created_at >= DATE '2026-03-01' AND created_at < DATE '2026-04-01' (correct answer)
  2. created_at BETWEEN DATE '2026-03-01' AND DATE '2026-03-31'
  3. created_at >= DATE '2026-03-01' AND created_at <= DATE '2026-03-31'
  4. created_at > DATE '2026-03-01' AND created_at < DATE '2026-04-01'
Explanation: When filtering timestamp columns by date range, the core challenge is that a DATE value like '2026-03-31' implicitly represents midnight — the very start of that day (00:00:00.000). Any timestamp later on March 31, such as 14:35:22 or 23:59:59.999, is greater than that date value, meaning comparisons using <= or BETWEEN with March 31 as the upper bound will silently drop those rows. Answer A solves this elegantly: created_at >= DATE '2026-03-01' AND created_at < DATE '2026-04-01' captures every moment from the first instant of March 1 up to — but not including — April 1. This half-open interval [start, end) pattern is the standard, precision-safe way to handle timestamp ranges because it requires no assumptions about fractional seconds. Answer B (BETWEEN DATE '2026-03-01' AND DATE '2026-03-31') is inclusive on both ends, so the upper bound is midnight on March 31, cutting off the entire rest of that day. BETWEEN with dates and timestamps is a frequent trap. Answer C makes the same mistake as B — using <= DATE '2026-03-31' means anything after midnight on March 31 is excluded. Answer D introduces a second error: > DATE '2026-03-01' (strictly greater than) would exclude orders placed exactly at midnight starting March 1, losing the very beginning of the month. The strategy to remember: always use a half-open interval — >= start AND < next_day — when filtering timestamps by date. Avoid BETWEEN and <= with timestamp columns unless you're certain the upper bound accounts for the full day.

Question 10

An events.occurred_utc column stores UTC timestamps. A report must include events whose local date in America/New_York was November 2, 2025. Daylight saving time ended that day: local midnight at the start of November 2 was 2025-11-02 04:00:00 UTC, and local midnight at the start of November 3 was 2025-11-03 05:00:00 UTC.

Which UTC predicate selects the complete local calendar day correctly?

  1. occurred_utc >= TIMESTAMP '2025-11-02 00:00:00' AND occurred_utc < TIMESTAMP '2025-11-03 00:00:00'
  2. occurred_utc >= TIMESTAMP '2025-11-02 04:00:00' AND occurred_utc < TIMESTAMP '2025-11-03 04:00:00'
  3. occurred_utc >= TIMESTAMP '2025-11-02 05:00:00' AND occurred_utc < TIMESTAMP '2025-11-03 05:00:00'
  4. occurred_utc >= TIMESTAMP '2025-11-02 04:00:00' AND occurred_utc < TIMESTAMP '2025-11-03 05:00:00' (correct answer)
Explanation: Whenever you filter timestamps by local calendar day, you need to think in UTC boundaries — specifically, what UTC moments correspond to local midnight at the start and end of that day. This gets tricky when Daylight Saving Time (DST) changes occur, because the UTC offset shifts mid-day. On November 2, 2025 in America/New_York, DST ends — clocks "fall back" from EDT (UTC−4) to EST (UTC−5). This means the day is 25 hours long in local time. Local midnight starting November 2 corresponds to 2025-11-02 04:00:00 UTC (because EDT is UTC−4), and local midnight starting November 3 corresponds to 2025-11-03 05:00:00 UTC (because EST is now UTC−5). So the correct UTC window is >= 2025-11-02 04:00:00 and < 2025-11-03 05:00:00, which is answer D. Choice A uses raw UTC midnight boundaries with no timezone conversion at all — it captures entirely the wrong UTC window and misses events that occurred during the actual local day. Choice B applies a consistent UTC−4 offset to both boundaries, as if EDT never ended. This cuts off the final hour of November 2 local time (from 2025-11-03 04:00:00 UTC to 2025-11-03 05:00:00 UTC), omitting events that happened between 11 PM and midnight EST. Choice C applies a consistent UTC−5 offset to both boundaries, as if EST were in effect all day. This shifts the start boundary an hour too late, dropping events from local midnight to 1 AM EDT (the first hour of the day). Your study tip: always derive UTC boundaries independently for each local midnight — never assume a single fixed offset for the whole day when a DST transition might occur.