Check if the current date is between two dates + mysql select query

Check if the current date is between two dates + mysql select query

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

Stefan Bogdanescu

Founder & Senior Architect

Why Your "Between" Query Returns Zero Rows

The query you are attempting to run fails because of a fundamental misunderstanding of how SQL interprets string literals versus column comparisons, combined with a specific syntax error in your date variable.

In your provided code snippet, $todaysDate is defined as "2012-26-11". There are two immediate issues here. First, the day component 26 does not exist in a standard Gregorian calendar; valid days range from 01 to 31. While some database engines might handle this gracefully by ignoring the overflow or treating it as an error depending on strict mode, relying on invalid data is never a strategy. Second, and more critically, you are injecting this string directly into the SQL statement without ensuring it matches the expected format of your dateStart and dateEnd columns.

If your database columns store dates in the format YYYY-MM-DD, MySQL expects the comparison value to match that format. However, even if you corrected the day to 2012-11-26 (assuming November 26th), the query structure itself is flawed. The syntax $todaysDate between dateStart and dateEnd implies a range check where $todaysDate is the subject, but the logic requires checking if the current date falls within the range defined by dateStart and dateEnd.

The correct logical operation is: current_date >= dateStart AND current_date <= dateEnd. When you use the BETWEEN operator, it is inclusive, which is generally what you want for time windows. However, the order of the columns in your original query attempt was ambiguous to the parser when mixed with string injection in that specific manner.

The Correct Syntax Pattern

To reliably check if a specific date falls within a range stored in a database, you must compare the target date against both boundaries using standard comparison operators or the BETWEEN keyword correctly applied.

Here is the corrected logic for your scenario. If you are working with raw SQL strings, ensure the date variable is formatted exactly as the column expects (e.g., '2012-11-26').

SELECT * 
FROM `table` 
WHERE active = 0 
  AND '2012-11-26' BETWEEN dateStart AND dateEnd;

Notice that the string literal includes single quotes. In your original PHP code, you concatenated $todaysDate directly into the query string without quotes. MySQL interprets 2012-26-11 as a numeric value (stripping non-digit characters until it hits the first dash or non-number), effectively turning your date into just the year 2012. When you compare the integer 2012 to a DATE column, MySQL often performs implicit type conversion that leads to unexpected results or simply fails to match rows because the types don't align as intended.

Using CURRENT_DATE for Dynamic Queries

If your goal is to check "today's" date rather than a hardcoded variable, you should avoid string concatenation for the date entirely. Instead, rely on SQL functions provided by the database engine. This approach eliminates formatting issues and ensures the comparison happens in the correct timezone or context depending on your server settings.

In MySQL, CURDATE() returns the current date without the time component. If you need to include time, use NOW().

SELECT * 
FROM `table` 
WHERE active = 0 
  AND CURDATE() BETWEEN dateStart AND dateEnd;

This is significantly more robust than injecting a PHP variable. It prevents SQL injection vulnerabilities and handles the formatting automatically. If your application layer (like Zend Framework in your example) manages connections, passing parameters safely using prepared statements is best practice, even if CURDATE() handles the value generation:

$sql = "SELECT * FROM `table` WHERE active = 0 AND CURDATE() BETWEEN dateStart AND dateEnd";
$result = $db->fetchAll($sql);

Data Type Considerations

A common pitfall in this type of query involves data types. Ensure that dateStart and dateEnd are stored as DATE, DATETIME, or TIMESTAMP. If they are stored as strings (e.g., VARCHAR(10)), MySQL will still compare them lexicographically if the format is consistent (YYYY-MM-DD). However, string comparison can break if the data contains leading zeros inconsistently (e.g., 2012-11-5 vs 2012-11-05) or if some rows contain invalid date strings.

If you are using a framework like Laravel, you would typically use Eloquent's query builder to handle these comparisons without writing raw SQL strings:

$today = Carbon\Carbon::now()->startOfDay(); // Optional: set start of day for inclusive match

$results = Table::where('active', 0)
    ->whereBetween(['dateStart', 'dateEnd'], $today->format('Y-m-d'))
    ->get();

Note that whereBetween in Laravel expects the value to be between the two bounds, but since your table has separate start and end columns, you would actually use whereDate or raw conditions:

$today = Carbon\Carbon::today()->format('Y-m-d');

$results = Table::where('active', 0)
    ->whereBetween(['dateStart', 'dateEnd'], [$today, $today]) // This syntax is for single column
    // Correct approach for separate columns:
    ->whereDate('dateStart', '<=', $today)
    ->whereDate('dateEnd', '>=', $today)
    ->get();

Conclusion

The zero rows returned by your initial query were likely caused by the missing single quotes around the date string and the invalid day value. By correcting the format, using CURDATE() for dynamic checks, or leveraging framework-specific query builders with proper date handling, you can ensure your queries return the expected records accurately. Always validate that your input strings match the column data types and formats to avoid silent failures in production environments.

Our network