How to Correctly Compare DATETIME and DATE Values in MySQL?

MySQL Date Time Comparison: Solve grammar errors and inaccurate results

When comparing the date in MySQL database, you must consider the data type and format to avoid errors and ensure that the results are accurate. A common challenge is to compare the DateTime column with the date format that does not include time component.

Question:

Try to use the function to compare the DateTime column and the date format that may cause SQL syntax errors.

Solution:

CONVERT()

In order to solve this problem, consider using the

function. This function only extract the date part from the DateTime column, or use the appropriate data type conversion function to convert the DateTime field into the date data type. alternative scheme:

DATE() <.> 1. Use the date () function:

<.> 2. Convert datetime to date:

<.> 3. Use Between and Interval:

<code class="language-sql">SELECT * FROM `players` WHERE DATE(us_reg_date) >= '2000-07-05' AND DATE(us_reg_date) < '2011-11-11';</code>

Another method is to use conditions and to specify the range that includes a date (excluding time component):

<code class="language-sql">SELECT * FROM `players` WHERE STR_TO_DATE(us_reg_date, '%Y-%m-%d') >= '2000-07-05' AND STR_TO_DATE(us_reg_date, '%Y-%m-%d') < '2011-11-11';</code>

By adopting these alternative strategies, you can effectively compare the DateTime column with the date format, while avoiding grammatical errors and ensuring the accuracy of MYSQL query mid -date comparison.

The above is the detailed content of How to Correctly Compare DATETIME and DATE Values in MySQL?. For more information, please follow other related articles on the PHP Chinese website!