Select data between two dates?

Select data between two dates?

  1. Blog
  2. 15 November 2026
  3. 2 min read

Stefan Bogdanescu

Founder & Senior Architect

Selecting Data Between Two Dates Requires Proper Formatting

When querying a database to retrieve records within a specific timeframe, the most common pitfall isn't the SQL syntax itself, but how the data is stored and compared. In your scenario, you are attempting to filter logs where the date column holds values in "MM/DD/YY" format, such as 01/01/12. Your current query constructs a string comparison:

$result = mysql_query("SELECT * FROM logs WHERE date >= '01/01/12' AND date <= '02/11/12'");

While the logic seems sound on paper, this approach relies entirely on lexicographical (string) ordering. In ASCII, the character 0 comes before 2, but once you reach single digits like 1, the comparison behavior can become unpredictable depending on how the string is parsed by the database engine versus standard string sorting rules. More critically, the mysql_* extension you are using is deprecated and removed in modern PHP versions, meaning this code will not run on current stacks without a migration to mysqli or PDO.

The String Comparison Trap

Your primary issue stems from treating dates as opaque strings. When a database sorts strings like '01/01/12', it compares them character by character.

  • Comparing 01/01/12 and 01/02/12 works fine because the month digits align.
  • However, if you had a date stored as 1/5/12 (without leading zeros), the string '1/5/12' would sort after '9/1/12' because 1 is greater than 9 in the first character position? No, actually 1 is less than 9. But consider 01/01/12 vs 1/01/12. If one record has a leading zero and another doesn't, the sort order breaks.

More importantly, if your data contains years like 13 (representing 2013) but you query for 2013, the string comparison fails completely because '1' is not equal to '2'. String sorting does not understand calendar time; it only knows ASCII values. To select data between two dates reliably, the database engine must treat the column as a temporal value, not a string of characters.

The Solution: Standardize Your Data Format

The robust solution is to ensure the date column stores data in an unambiguous format that the database understands natively. In MySQL and PostgreSQL, this means using a DATE or DATETIME type column, which typically stores values as YYYY-MM-DD.

If you are stuck with legacy data stored as strings (like "MM/DD/YY"), you have two options: keep the string format but adjust your query logic to handle it safely, or migrate the data. Migration is always the preferred path for long-term maintainability.

Option 1: Migrate to Native Date Types

Converting your column to a DATE type allows you to use standard date literals that the database interprets correctly, regardless of your application's codebase.

ALTER TABLE logs MODIFY COLUMN date DATE;

-- Populate with standardized dates if needed
UPDATE logs SET date = STR_TO_DATE(date, '%m/%d/%y');

Once converted, you can query using standard ISO 8601 format:

$from_date = "2012-01-01";
$to_date = "2012-02-11";

$result = $mysqli->query(
    "SELECT * FROM logs 
     WHERE date BETWEEN ? AND ? 
     ORDER BY id DESC", 
    [$from_date, $to_date]
);

Using prepared statements here is critical. It prevents SQL injection and ensures the database treats your inputs strictly as dates, not strings or numbers.

Option 2: Querying Legacy String Data

If you cannot migrate the data immediately, you must query using the exact format stored in the database to ensure lexicographical order matches chronological order. Since "MM/DD/YY" sorts correctly if all months and days are zero-padded (e.g., 01 instead of 1), your original logic holds only if every single record follows that strict padding rule.

// Strictly matching the stored format 'MM/DD/YY'
$from_date = "01/01/12";
$to_date = "02/11/12";

$result = $mysqli->query(
    "SELECT * FROM logs 
     WHERE date BETWEEN ? AND ? 
     ORDER BY id DESC",
    [$from_date, $to_date]
);

Note the use of BETWEEN. It is syntactic sugar for >= and <=, but it explicitly signals intent to read data between boundaries inclusive of both ends.

Modernizing Your Stack

Finally, you must address the underlying technology. The mysql_query() function was deprecated in PHP 5.5.0 and removed in PHP 7.0.0. Continuing to use it will cause fatal errors on any modern server environment. You should migrate your connection logic to MySQLi or PDO immediately. This migration not only fixes your date handling but also provides better support for prepared statements, which are essential when dealing with user input or dynamic date ranges.

For projects requiring rapid development and robust database integration, consider frameworks like Laravel. They abstract these complexities behind elegant interfaces, ensuring that date handling is secure and consistent out of the box. You can find more resources on modernizing your PHP stack and best practices at https://laravelcompany.com.

Our network