How to compare only day and month with date field in mysql?
For example, I've a date in one table: 2014-07-10
Similarly, another date 2000-07-10
in another table.
I want to compare only day and month is equal on date field.
I tried this format. But I can't get the answer
select *
from table
where STR_TO_DATE(field,'%m-%d') = STR_TO_DATE('2000-07-10','%m-%d')
and id = "1"
To count the difference between dates in MySQL, use the DATEDIFF(enddate, startdate) function. The difference between startdate and enddate is expressed in days.
If you want to get a day from a date in a table, use the SQL Server DAY() function. This function takes only one argument – the date. This can be a date or date and time data type.
The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".
Use DATE_FORMAT instead:
SELECT DATE_FORMAT('2000-07-10','%m-%d')
yields
07-10
Here's your query re-written with DATE_FORMAT()
:
SELECT *
FROM table
WHERE DATE_FORMAT(field, '%m-%d') = DATE_FORMAT('2000-07-10', '%m-%d')
AND id = "1"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With