Comparing Date from Variable to MySQL Date

Comparing date from variable to MySQL date

Lets create a variable that holds a date and then convert it to date format that MySQL can understand.

$dt = date('Y-m-d', strtotime('1995-02-27')); // maybe filled dynamically
 $sql = "SELECT event FROM LifeEvents WHERE event_date > '$dt'";
  $result = $conn->query($sql);

  if ($result->num_rows > 0) {
      // output data of each row
      while($row = $result->fetch_assoc()) {
          echo $row["event"] . "<br>";
      }
  } else {
      echo "0 results";
  }
  $conn->close();

Then change what you want in order to serve your needs.
Hope I pushed you further.

MySQL compare DATE string with string from DATETIME field

Use the following:

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

Just for reference I have a 2 million record table, I ran a similar query.
Salils answer took 4.48 seconds, the above took 2.25 seconds.

So if the table is BIG I would suggest this rather.

Compare dates in MySQL

You can try below query,

select * from players
where 
    us_reg_date between '2000-07-05'
and
    DATE_ADD('2011-11-10',INTERVAL 1 DAY)

Difference comparing dates in MariaDB and MySQL

You should change the query. You are suggesting that '2021-07-14 24:00:00' and '2021-07-15 00:00:00' represent the same value.
Try using this instead:

SELECT * FROM table WHERE start_date >= '2021-07-14 00:00:00' AND start_date <= '2021-07-14 23:59:59'

You could also reduce number of literals in your query:

SELECT * FROM table WHERE DATE(start_date) = '2021-07-14'

How to compare two dates from MYSQL using PHP

Just let de database handle it for you:

$sql = "SELECT name, date1, date2, IF(date1 < date2, date1, date2) FROM users";

Now $row[3] always contains the smallest date.

[Edit]

As OP only later mentioned the dates could also have a NULL value, the query needs adjusting. It now always returns the other date if one of the dates is empty, or NULL if both dates are empty.

SELECT id, date1, date2, 
  // test if one of the dates (or both) is NULL
  // if so return the one date, or NULL if both are empty
IF(date1 IS NULL OR date2 IS NULL, COALESCE(date1, date2),
  // else compare dates
IF(date1 < date2, date1, date2)
) FROM test

How to compare PHP date with MySQL date

So Finally I Figured it out so Here is the code if anyone Need's it.

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "event";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);

} 
date_default_timezone_set("Europe/London");

$compare = date("Y-m-d");
/*echo $compare;*/
$sql = "SELECT date FROM event_table WHERE date = '$compare'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "Date: " . $row["date"]. "<br>";
        echo '<img src="C:\Apache24\htdocs\Teying\yes_logo.png alt="icon" />';
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Compare current date with date from MySQL DB using Java

This can be achieved by using the correct MySQL datatype and statement.
As I mention in a comment, I recommend using the BIGINT datatype of the expiry_date column.
In your program, you will want to do the following:

long currentTime = Calendar.getInstance().getTime().getTime();
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM Checkout where expiry_date < ?");
stmt.setLong(1, currentTime);
ResultSet rs2 = stmt.executeQuery();

This compares the value in the database, which should look something like 1589522424053 to the value of the currentTime variable, which should look something like 1589532467163.

Unexpected result when comparing dates

MySQLs implicit type conversion can be very surprising. If you want to understand the behavior of your queries, you can try to apply the type conversion rules as described in Type Conversion in Expression Evaluation. However - I failed to do that for your case. For example: For the two expressions date > '2019' and date > 2019 I would apply the following rule:

If one of the arguments is a TIMESTAMP or DATETIME column and the
other argument is a constant, the constant is converted to a timestamp
before the comparison is performed.

But that cannot be the case, because neither the number 2019 nor the string '2019' can be converted to a temporal type. Here is a query, which demonstrates some implicit conversions:

select '2019' + interval 0 day -- implicit cast to date(time)
     , 2019 + interval 0 day
     , 20190101 + interval 0 day
     , 190101 + interval 0 day
     , '2019*01*01' + interval 0 day
     , '2019-01-01' + interval 0 day
     , '2019-01-01' + 0 -- implicit cast to numeric
     , date('2019-01-01') + 0
     , date('2018-01-01') > 2019
     , date('2018-01-01') > '2019'
;

Result:

Expression                    | Result
------------------------------|-----------
'2019' + interval 0 day       | null
2019 + interval 0 day         | null
20190101 + interval 0 day     | 2019-01-01
190101 + interval 0 day       | 2019-01-01
'2019*01*01' + interval 0 day | 2019-01-01
'2019-01-01' + interval 0 day | 2019-01-01
'2019-01-01' + 0              | 2019
date('2019-01-01') + 0        | 20190101
date('2018-01-01') > 2019     | 1
date('2018-01-01') > '2019'   | 0

As you see, when we try to convert 2019 or '2019' to a date (or datetime), we get NULL. Thus the conditions should also be evaluated to NULL and the result set would be empty. But as we know, that is not the case. Maybe I'm just wrong, assuming that 2019 and '2019' are constants. But then I don't know what they could mean.

So I can only make assumptions. And my assumtion is: Whenever one comparator is numeric, the other value is also converted to a numeric value. This would be the case for date > 2019 aswell as for date > year(@THIS_YEAR). In this case the date 2018-01-01 is converted to 20180101 (see the table above), which (in numeric context) is greater than 2019. So you get rows from the year 2018.

For date > '2019' I can only assume, that the values are compared as strings. And '2018-01-01' as string is considered "smaller" than 2019.

But even if that behavior would be properly documented, the rules are too difficult to remember, because one can hardly see any logic behind them. (I don't say - there is no logic - I just don't see any.)

So I can give you one advise: If you want to compare two incompatible types, always cast or convert them to be compatible.

WHERE year(date) >= year(@THIS_YEAR)

would be fine, since you compare two numeric values. But that is not necessery in your case and you can just use

WHERE date >= @THIS_YEAR

because 2019-01-01 00:00:00 in

`SET @THIS_YEAR = "2019-01-01 00:00:00";`

is a perfectly formatted DATETIME string and can be considered compatible with the DATETIME type. '2019-01-01' would be just fine aswell.

Note that if you wrapp a column into a function call (like year(date)) you will loose the ability to use an index on that column.

---

Related Topics