Developer ToolsFree generatorPDF, Excel, Word export

SQL Date Range Filter Generator for ERP Tables

Most wrong month-end numbers in ERP reports come from the date filter, not the data. This generator writes the same filter in four dialects using a half-open range, which is the pattern that survives timestamps, time zones and leap years.

Fill in

Table name

Date column

Start date (inclusive)

End date (exclusive, the day AFTER the last day wanted)

Columns to select

Rolling window in days

Generated code

Download:

SQL Server (T-SQL)

SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= '2026-10-01'
  AND trans_date <  '2026-11-01';

-- Rolling window, midnight aligned
SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= DATEADD(day, -30, CAST(GETDATE() AS date))
  AND trans_date <  DATEADD(day, 1, CAST(GETDATE() AS date));

Oracle

SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= DATE '2026-10-01'
  AND trans_date <  DATE '2026-11-01';

-- Rolling window
SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= TRUNC(SYSDATE) - 30
  AND trans_date <  TRUNC(SYSDATE) + 1;

PostgreSQL

SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= DATE '2026-10-01'
  AND trans_date <  DATE '2026-11-01';

-- Rolling window
SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= CURRENT_DATE - INTERVAL '30 days'
  AND trans_date <  CURRENT_DATE + INTERVAL '1 day';

MySQL / MariaDB

SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= '2026-10-01'
  AND trans_date <  '2026-11-01';

-- Rolling window
SELECT item, whse, qty, trans_date
FROM inventory_transactions
WHERE trans_date >= CURDATE() - INTERVAL 30 DAY
  AND trans_date <  CURDATE() + INTERVAL 1 DAY;

Always filter the raw column, never wrap it in a function such as CONVERT or TRUNC, or the index on the date column cannot be used. Check whether your ERP stores dates as true date types, as integers (for example YYYYMMDD) or as separate year and period columns before pasting.

Get your recurring ERP reports reviewed

Send us your three slowest or most error-prone report queries and we will review the filters and indexes. Optional, no obligation.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter your table and date column, the first day you want, and the day after the last day you want. The generator fills in SQL Server, Oracle, PostgreSQL and MySQL versions, plus a rolling window variant that always ends at tomorrow's midnight. Copy the block that matches your ERP database and run it in a read-only session first. If your ERP is SyteLine, Epicor, Visual or Dynamics on SQL Server, use the T-SQL block. Oracle EBS, JD Edwards on Oracle and Fusion use the Oracle block.

Start is inclusive, end is exclusive
Pick the end as the first day of the next month
Use the rolling window for dashboards, fixed dates for audits

Why half-open ranges beat BETWEEN

BETWEEN is inclusive on both ends. If a column holds date and time, BETWEEN '2026-10-01' AND '2026-10-31' stops at midnight on the 31st and silently drops every transaction posted later that day. Adding 23:59:59 only moves the problem, because fractional seconds and datetime2 precision still leave gaps. A range of >= start and < next-day-start has no gap and no overlap, so adjacent months always add up to the quarter.

Reading the result and checking it

After running the query, check the minimum and maximum of the date column in the result. The minimum should be at or after your start date and the maximum strictly before the exclusive end. Then run the same filter for the adjacent period and confirm the row counts add up to the combined range. If your ERP stores dates in local time and your server runs in UTC, shift the boundaries or the first and last hours of the month will land in the wrong period.

Common mistakes

Wrapping the column in a function to strip time, for example CONVERT(date, trans_date) = '2026-10-01', forces a scan on large transaction tables. Comparing a text column to a date works in testing and breaks when someone saves a date in a different format. Hard-coding the current year into saved reports means they quietly go stale each January. Parameterize the dates in your reporting tool instead.

Do not use BETWEEN on datetime columns
Do not apply functions to the indexed column
Confirm whether the column is date, datetime or an integer
Check the server time zone against the business time zone

Frequently Asked Questions

Why is my SQL BETWEEN missing the last day of the month?

If the column includes a time part, BETWEEN '2026-10-01' AND '2026-10-31' means midnight at the start of the 31st, so anything later that day is excluded. Use >= the first day and < the first day of the next month instead.

Is a half-open range slower than BETWEEN?

No. Both forms are simple range predicates on the raw column and use the same index seek. The half-open form is only about correctness and having no overlap between adjacent periods.

What if my ERP stores dates as integers like 20261001?

Compare against integers in the same format, for example col >= 20261001 AND col < 20261101. Convert only the constants, never the column. Some ERPs use Julian style integers, so check the column definition first.

Which block should I use for SQL Server based ERPs?

Use the first block. Infor SyteLine, Epicor, Dynamics GP and many shop floor systems run on SQL Server, so the T-SQL form with DATEADD and CAST applies. Always test in a read-only or reporting copy first.

If you keep rewriting these filters by hand for every report, we can put a governed question layer over your ERP database so analysts ask in plain language and see the exact SQL used.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Developer Tools tools

See all

Back to All Tools