# Test your SQL date filter with four boundary rows

A December returns report should include a return accepted at the very end of December 31. It should exclude a return accepted at midnight on January 1. Those two rows are enough to expose a date filter that looks right on ordinary daytime data.

Before checking the full report, try four deliberately chosen timestamps: just before the month, at its start, near its end, and at the start of the next month. Here is a complete PostgreSQL query; it creates a temporary result set with `VALUES`, so you do not need a table.

```sql
WITH returns (return_id, accepted_at) AS (
  VALUES
    (701, TIMESTAMP '2026-11-30 23:59:59.999999'),
    (702, TIMESTAMP '2026-12-01 00:00:00'),
    (705, TIMESTAMP '2026-12-31 23:59:59.999999'),
    (706, TIMESTAMP '2027-01-01 00:00:00')
)
SELECT
  return_id,
  accepted_at BETWEEN TIMESTAMP '2026-12-01'
                  AND TIMESTAMP '2026-12-31' AS date_end,
  accepted_at >= TIMESTAMP '2026-12-01'
    AND accepted_at <= TIMESTAMP '2026-12-31 23:59:59' AS last_second,
  accepted_at >= TIMESTAMP '2026-12-01'
    AND accepted_at < TIMESTAMP '2027-01-01' AS next_month
FROM returns
ORDER BY return_id;
```

Each Boolean column answers: would this version of the filter keep the row?

| return_id | date_end | last_second | next_month |
| --- | --- | --- | --- |
| 701 | false | false | false |
| 702 | true | true | true |
| 705 | false | false | true |
| 706 | false | false | false |

`BETWEEN` includes both endpoints. In `date_end`, the upper endpoint is midnight at the **start** of December 31, so the return near the end of that day falls outside it. Changing the endpoint to `23:59:59` still misses row 705: PostgreSQL timestamps can contain fractional seconds.

The third filter uses the start of January as an exclusive endpoint. It keeps the December return without needing to name the last possible instant of December:

```sql
WHERE accepted_at >= TIMESTAMP '2026-12-01'
  AND accepted_at <  TIMESTAMP '2027-01-01'
```

January can then begin with `accepted_at >= TIMESTAMP '2027-01-01'`. The row at exactly midnight belongs to January, and the two monthly ranges do not overlap. This is a **half-open interval**: the start is included and the end is excluded.

These examples use `timestamp without time zone`. If your report uses `timestamptz`, first define which business time zone determines the month and construct the boundaries for that zone. Changing an operator does not settle the time-zone decision.

For a real report, keep the four boundary cases alongside your usual sample data and check the actual row IDs. A plausible total can hide an excluded row or a row assigned to the wrong period.

You can [try the date filters in SQLBrief](https://sqlbrief.com/en/guides/sql-date-boundaries?utm_source=hashnode&utm_medium=referral&utm_campaign=sqlbrief-launch&utm_content=p1_hn_gd_20261115_001). The browser editor has the data ready: compare the filters, then write a December query against a larger returns table. No installation or account is needed.

PostgreSQL reference: [comparison predicates, including BETWEEN](https://www.postgresql.org/docs/current/functions-comparison.html) and [date/time types](https://www.postgresql.org/docs/current/datatype-datetime.html).

