CODEWITHSUNDEEP
phpmysql
Hectorubie
Hectorubie

Reputation: 85

MySQL query between 2 dates fields

i am trying to create a MySQL query that will take current date and compare it to the 2 date fields that i have in my table and return the rows that satisfy that query.

here is my columns 1- [from_date] which type is Date 2- [to_date] which also holds type Date

the query suppose to return the rows that falls in between those dates.

here is my query

mysql_query("SELECT * FROM location WHERE from_date >= DATE() AND to_date <= DATE()")

my problem is that it doesn't return anything. should i switch my column type to DATETIME?

Thanks in advance.

Upvotes: 5

Views: 3085

Answers (5)

FrancisMarfo
FrancisMarfo

Reputation: 143

See documentation here.
Use BETWEEN
mysql documentation here

Upvotes: 2

Sirko
Sirko

Reputation: 74106

The DATE() function actually just extracts the date part from a date or datetime expression. It doesn't, however, not return the current date as you intend.

To get the current time use NOW() or CURDATE():

mysql_query("SELECT * FROM location WHERE CURDATE() BETWEEN from_date AND to_date")

If trying to find a something between two values, MySQL also has the BETWEEN operator

Upvotes: 0

CuteBabyMannu
CuteBabyMannu

Reputation: 35

use between

mysql_query("SELECT * FROM location WHERE CURDATE() between from_date and to_date

Upvotes: 1

Shijin TR
Shijin TR

Reputation: 7788

Try NOW() insted of DATE() 

    mysql_query("SELECT * FROM location WHERE from_date >= NOW() AND to_date <= NOW()")

Get more about NOW() here

Upvotes: 0

Ahmed Z.
Ahmed Z.

Reputation: 2337

You should use Now() to get the current date of the system.

Upvotes: 4

Related Questions