CODEWITHSUNDEEP
phpmysql
Rakesh Mishra
Rakesh Mishra

Reputation: 375

Mysql show only the messages in between dates

I have my mysql database table messages is like this

id   message    start_date    end_date
1    message1   2013-09-29    2013-11-30
2    message2   2013-08-20    2013-09-30
3    message3   2013-06-20    2014-01-01
4    message4   2013-06-06    2013-09-20
5    message5   2013-10-25    2014-03-05

I want to show the message when the start_date is equal to today and today is greater than start_date and also in the same way it should show the message ``before end_date. Once the end_date has been crossed then it should not show the messages. For that I have done like this

SELECT * FROM messages WHERE start_date >= CURDATE( )  and end_date >= CURDATE( );

But this one is showing all the messages whose start date has not come. Like the row 5th(5 message5 2013-10-25 2014-03-05). So I want only the messages in between the start_date is today and start_date has been crossed and it will show until end_date. So can someone kindly tell me how to do this? Any help and suggestions will be really appreciable. Thanks

Upvotes: 1

Views: 221

Answers (1)

Sébastien
Sébastien

Reputation: 12139

I think this is what you need:

Edit:

SELECT * FROM messages WHERE NOW() BETWEEN start_date AND end_date;

SELECT * FROM messages WHERE CURDATE() BETWEEN start_date AND end_date;

FYI, in your original query you only have to change the direction of the operator when comparing start_date :

SELECT * FROM messages WHERE start_date <= CURDATE() and end_date >= CURDATE();

Upvotes: 1

Related Questions