How to select from MYSQL database WHERE its only from two DATETIME?

Question

I have a tabel in MYSQL database that has 2 columns.

1- start_date
2- end_date

the above columns both hold DATETIME like so:

start_date                  end_date

2019-10-03 13:30:00     2020-10-03 23:30:00

I need to select from the MYSQL table ONLY if the current date is between the start_date and end_date.

So i tried this MYSQL Query:

SELECT * FROM table WHERE `end_date` >= CURDATE() AND `start_date` <= CURDATE()

but this doesn't work and doesn't return anything from the database even thogh the the current date is between the start_date and end_date.

Could someone please advice on this?

No date can be greater than today >= CURDATE() and less than today <= CURDATE() at the same time. — Jay Blanchard
– Jay Blanchard, Commented Oct 3, 2019 at 12:33
@JayBlanchard, when it comes to dates, my head starts making strange noises! don't know why. — sam sexton
– sam sexton, Commented Oct 3, 2019 at 12:34

Dilip Hirapara · Accepted Answer · 2019-10-03 12:48:51Z

1

Q: I need to select from the MYSQL table ONLY if the current date is between the start_date and end_date.

Try this.

SELECT * FROM `table` WHERE CURDATE() between start_date and end_date

Convert it with date before because you're using their timestamp.

SELECT * FROM `table` WHERE CURDATE() between date(start_date) and date(end_date)

Or

SELECT * FROM `table` WHERE NOW() between start_date and end_date

edited Oct 3, 2019 at 12:48

answered Oct 3, 2019 at 12:35

Dilip Hirapara

15.4k3 gold badges34 silver badges56 bronze badges

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

9 Comments

sam sexton Over a year ago

Jeeez. that easy?

Jay Blanchard Over a year ago

Yep, it's just that easy.

sam sexton Over a year ago

But that doesn't return anything either! the date i have are start_date 2019-10-03 13:30:00 AND end_date 2020-10-03 23:30:00. I would expect that to be returned since the currect date is between those two dates!

Dilip Hirapara Over a year ago

Try make date(end_date) and date(start_date).

Strawberry Over a year ago

Note that the second query cannot use an index :-( (but the third option can) :-)

|

Veerender Mishra · Accepted Answer · 2019-10-03 12:39:53Z

1

Using SQL date function in query

SELECT * FROM user WHERE date(end_date) >= CURDATE() AND date(start_date) <= CURDATE()

answered Oct 3, 2019 at 12:39

Veerender Mishra

881 silver badge12 bronze badges

Comments

Krishna Jangid · Accepted Answer · 2019-10-03 12:39:23Z

0

try this

SELECT * FROM table 
WHERE start_date >= '2019-10-03 13:30:00' 
AND end_date <= '2020-10-03 23:30:00'

answered Oct 3, 2019 at 12:39

Krishna Jangid

5,5166 gold badges33 silver badges37 bronze badges

1 Comment

sam sexton Over a year ago

the dates are stored in the MYSQL. cant hardcode them in my php query.

Collectives™ on Stack Overflow

How to select from MYSQL database WHERE its only from two DATETIME?

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related