MySQL query between 2 dates fields

Question

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.

if you got your answer you should accept the answer buddy. its just not like you come, ask a question, get your answer and run away.. — Ahmed Z.
– Ahmed Z., Commented May 17, 2013 at 19:43
sorry new to this forum, dont know where is the answer buddy, ive been looking for it but cant find it. can you point me to it? — Hectorubie
– Hectorubie, Commented May 20, 2013 at 13:34

Ahmed Z. · Accepted Answer · 2013-05-17 13:12:10Z

4

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

answered May 17, 2013 at 13:12

Ahmed Z.

2,33726 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

NOW() gives current date and time. CURRENT_DATE() gives the current date.

FrancisMarfo · Accepted Answer · 2013-05-17 13:47:32Z

2

See documentation here.
Use BETWEEN
mysql documentation here

edited May 17, 2013 at 13:47

answered May 17, 2013 at 13:14

FrancisMarfo

1436 bronze badges

Comments

CuteBabyMannu · Accepted Answer · 2013-05-17 13:14:14Z

1

use between

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

answered May 17, 2013 at 13:14

CuteBabyMannu

351 silver badge8 bronze badges

Comments

Shijin TR · Accepted Answer · 2013-05-17 13:12:40Z

0

Try NOW() insted of DATE()

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

Get more about NOW() here

answered May 17, 2013 at 13:12

Shijin TR

7,80911 gold badges64 silver badges126 bronze badges

1 Comment

Hectorubie Over a year ago

Thanks everyone, i went with mysql_query("SELECT * FROM location WHERE CURDATE() BETWEEN from_date AND to_date") and it worked great.

Sirko · Accepted Answer · 2013-05-17 13:15:12Z

0

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

answered May 17, 2013 at 13:15

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Collectives™ on Stack Overflow

MySQL query between 2 dates fields

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related