MySQL Date Range Queries: BETWEEN Dates Safely

Use MySQL BETWEEN to select values inside an inclusive range. For a DATE column, both boundary dates are included.

SELECT id,
       dt2
FROM dt_tb
WHERE dt2 BETWEEN '2005-01-01'
                    AND '2005-12-31'
ORDER BY dt2;

For DATETIME or TIMESTAMP columns, a half-open range is usually safer:

SELECT id,
       dt
FROM dt_tb
WHERE dt >= '2005-01-01'
  AND dt <  '2006-01-01'
ORDER BY dt;

BETWEEN Is Inclusive Top ↑

BETWEEN includes both the lower and upper boundaries.

WHERE dt2 BETWEEN '2005-01-01'
              AND '2005-12-31'

is equivalent for a DATE column to:

WHERE dt2 >= '2005-01-01'
  AND dt2 <= '2005-12-31'

Modern Sample Table Top ↑

The original tutorial used INT(2) and zero-date defaults. Modern MySQL installations commonly use strict SQL modes, so use real date values and avoid zero dates as placeholders.

CREATE TABLE dt_tb (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    dt DATETIME NOT NULL,
    dt2 DATE NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

INSERT INTO dt_tb (id, dt, dt2)
VALUES
(1, '2004-10-26 00:00:00', '2005-01-25'),
(2, '2004-05-05 23:56:25', '2005-06-12'),
(3, '2005-12-08 13:20:10', '2005-06-06'),
(4, '2003-05-26 00:00:00', '2007-12-18'),
(5, '2007-12-18 00:00:00', '2003-08-16');

Between Two DATE Values Top ↑

SELECT id,
       dt2
FROM dt_tb
WHERE dt2 BETWEEN '2005-01-01'
                    AND '2005-12-31'
ORDER BY dt2;

This returns every DATE value from January 1 through December 31, 2005, including both dates.

Between Two DATETIME Values Top ↑

Suppose you want every DATETIME value in 2005. Prefer:

SELECT id,
       dt
FROM dt_tb
WHERE dt >= '2005-01-01'
  AND dt <  '2006-01-01'
ORDER BY dt;

The exclusive upper boundary automatically includes all times on December 31, including fractional seconds if the column stores them.

Records Between Two Years Top ↑

The original page used YEAR(dt2) BETWEEN 2004 AND 2005:

SELECT id,
       dt2
FROM dt_tb
WHERE YEAR(dt2) BETWEEN 2004 AND 2005;

This is logically correct and includes both years. When the column is indexed and you know the actual date boundaries, a direct range is usually more index-friendly:

SELECT id,
       dt2
FROM dt_tb
WHERE dt2 >= '2004-01-01'
  AND dt2 <  '2006-01-01';

See YEAR() and year extraction.

Records Between Months Top ↑

If you intentionally want February through August from every year, extracting the month is appropriate:

SELECT id,
       dt
FROM dt_tb
WHERE MONTH(dt) BETWEEN 2 AND 8
ORDER BY dt;

This matches February through August regardless of year.

See MONTH() and month extraction.

Range Across Month and Year Top ↑

For one continuous range, such as March 2015 through all of February 2016, use real date boundaries:

SELECT id,
       dt
FROM dt_tb
WHERE dt >= '2015-03-01'
  AND dt <  '2016-03-01'
ORDER BY dt;

For a DATE column, the original inclusive pattern is also valid:

SELECT id,
       dt2
FROM dt_tb
WHERE dt2 BETWEEN '2015-03-01'
                    AND LAST_DAY('2016-02-01');

See LAST_DAY().

Ranges Relative to Today Top ↑

Use CURDATE() with INTERVAL arithmetic for rolling date ranges.

Last 10 days through today Top ↑

For a DATE column:

SELECT id,
       date
FROM dt_table
WHERE date BETWEEN CURDATE() - INTERVAL 10 DAY
                      AND CURDATE()
ORDER BY date;

Last one month through today Top ↑

SELECT id,
       date
FROM dt_table
WHERE date BETWEEN CURDATE() - INTERVAL 1 MONTH
                      AND CURDATE();

Last one year through today Top ↑

SELECT id,
       date
FROM dt_table
WHERE date BETWEEN CURDATE() - INTERVAL 1 YEAR
                      AND CURDATE();

Three to six months ago Top ↑

For a DATE column:

SELECT id,
       date
FROM dt_table
WHERE date BETWEEN CURDATE() - INTERVAL 6 MONTH
                      AND CURDATE() - INTERVAL 3 MONTH;

For DATETIME reporting buckets, decide explicitly whether touching boundaries should belong to both adjacent buckets. Half-open ranges avoid double counting at shared boundaries.

Current Month and Month-to-Date Top ↑

Full current calendar month Top ↑

SELECT id,
       date
FROM dt_table
WHERE date >= DATE_FORMAT(
                  CURDATE(),
                  '%Y-%m-01'
              )
  AND date < DATE_FORMAT(
                 CURDATE() + INTERVAL 1 MONTH,
                 '%Y-%m-01'
             );

Month-to-date through today Top ↑

SELECT id,
       date
FROM dt_table
WHERE date >= DATE_FORMAT(
                  CURDATE(),
                  '%Y-%m-01'
              )
  AND date < CURDATE() + INTERVAL 1 DAY;

The original page described first-of-month through today as "present month." The distinction matters: month-to-date stops at today, while a full-month query also covers future dates in the same month.

Current Year Top ↑

A simple expression is:

SELECT id,
       date
FROM dt_table
WHERE YEAR(date) = YEAR(CURDATE());

For an indexed date column, prefer a direct range:

SELECT id,
       date
FROM dt_table
WHERE date >= DATE_FORMAT(
                  CURDATE(),
                  '%Y-01-01'
              )
  AND date < DATE_FORMAT(
                 CURDATE() + INTERVAL 1 YEAR,
                 '%Y-01-01'
             );

Current, Previous and Next Week Top ↑

The original page used WEEKOFYEAR(date) = WEEKOFYEAR(CURDATE()) +/- 1. That can break around year boundaries. A boundary-based query is more robust.

Current week, Monday through next Monday Top ↑

SELECT id,
       date
FROM dt_table
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
  AND date <  CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
                   + INTERVAL 7 DAY;

Previous week Top ↑

SELECT id,
       date
FROM dt_table
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
                   - INTERVAL 7 DAY
  AND date <  CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY;

Next week Top ↑

SELECT id,
       date
FROM dt_table
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
                   + INTERVAL 7 DAY
  AND date <  CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
                   + INTERVAL 14 DAY;

Working Days of This Week Top ↑

WEEKDAY() returns Monday = 0 through Sunday = 6. Therefore Monday through Friday is 0 through 4.

SELECT id,
       date
FROM dt_table
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
  AND date <  CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
                   + INTERVAL 7 DAY
  AND WEEKDAY(date) BETWEEN 0 AND 4;

Working days from Monday through today Top ↑

SELECT id,
       date
FROM dt_table
WHERE date >= CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY
  AND date <  CURDATE() + INTERVAL 1 DAY
  AND WEEKDAY(date) BETWEEN 0 AND 4;

User-selected From and To Dates Top ↑

When users select both a start date and an end date, validate the inputs and bind them as values. For a DATETIME column, convert the inclusive end date into the exclusive start of the next day.

If the user chooses:

From: 2026-09-01
To:   2026-09-09

query a DATETIME column as:

WHERE created_at >= '2026-09-01'
  AND created_at <  '2026-09-10'

This includes every time on September 9.

The existing Plus2net tools demonstrate building these ranges from calendar inputs:

Generate Query Using Dates from Calendar →

Generate Query Using Date and Time →

Only From Date or Only To Date Top ↑

Not every search form needs both boundaries.

Only a From date Top ↑

SELECT id,
       created_at
FROM orders
WHERE created_at >= '2026-09-01'
ORDER BY created_at;

Only a To date, inclusive through that calendar day Top ↑

SELECT id,
       created_at
FROM orders
WHERE created_at < '2026-09-10'
ORDER BY created_at;

If the user selected September 9 as the To date, September 10 is the exclusive boundary.

Convert Input Date Formats in PHP Top ↑

If an external value is supplied as DD-MM-YYYY, parse the expected format rather than relying on PHP to guess it.

<?php
$input='09-09-2026';

$date=DateTimeImmutable::createFromFormat(
    'd-m-Y',
    $input
);

if($date === false){
    echo 'Invalid date.';
}else{
    $mysql_date=$date->format('Y-m-d');
}

See inserting and validating date values.

Prepared PDO Date-range Query Top ↑

For a DATETIME column and an inclusive user-selected end date:

<?php
$from=DateTimeImmutable::createFromFormat(
    'Y-m-d',
    '2026-09-01'
);
$to=DateTimeImmutable::createFromFormat(
    'Y-m-d',
    '2026-09-09'
);

if($from === false || $to === false || $from > $to){
    echo 'Invalid date range.';
}else{
    $start=$from->format('Y-m-d');
    $end=$to->modify('+1 day')
            ->format('Y-m-d');

    $sql="SELECT id, created_at
          FROM orders
          WHERE created_at >= :start
            AND created_at < :end
          ORDER BY created_at";

    $stmt=$dbo->prepare($sql);
    $stmt->bindValue(':start', $start, PDO::PARAM_STR);
    $stmt->bindValue(':end', $end, PDO::PARAM_STR);
    $stmt->execute();
}

Do not interpolate date strings directly into SQL. Bind them as data values.

Index-friendly Date Filtering Top ↑

If created_at is indexed, this form is usually easier for a normal B-tree index to use:

WHERE created_at >= '2026-09-01'
  AND created_at <  '2026-10-01'

than wrapping the indexed column in functions:

-- Useful for some reporting tasks, but often less index-friendly
WHERE MONTH(created_at) = 9
  AND YEAR(created_at) = 2026

• Use actual date boundaries when filtering one specific contiguous period.
• Use MONTH()/YEAR() when the requirement really is based on extracted calendar parts across many years.
• Avoid DATE(created_at) = ... on a large indexed DATETIME column when a direct range can express the same condition.
• Use EXPLAIN and real measurements for important production queries.

Common Date-range Mistakes Top ↑

Using an inclusive date literal as the end of a DATETIME range Top ↑

BETWEEN '2026-09-01' AND '2026-09-09' does not include the whole final day for DATETIME values. Prefer an exclusive next-day boundary.

Using 23:59:59 as a universal end-of-day value Top ↑

Fractional seconds can exist. The next-day exclusive boundary is simpler and safer.

Confusing month-to-date with the full month Top ↑

First-of-month through CURDATE() stops at today. A full calendar month ends at the first day of the next month, exclusive.

Using YEAR()/MONTH() when a range is clearer Top ↑

Functions on the indexed column can make ordinary index use harder. Use direct boundaries when selecting one known period.

Using WEEKOFYEAR() +/- 1 around year boundaries Top ↑

Week numbers reset at the new year. Date boundaries based on the current week's Monday are more robust.

Counting Saturday as a working day accidentally Top ↑

MySQL WEEKDAY() returns Monday = 0 through Sunday = 6. Monday-Friday is 0-4, not 0-5.

Using zero dates in the sample schema Top ↑

Use valid dates or NULL where an absent date is allowed.

Concatenating form dates directly into SQL Top ↑

Validate the date format and bind the values with a prepared statement.

Video Tutorial Top ↑

Demos, Generator Tools and Exercises Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: