SQL query to select row where current date and time is between fields start and end

Question

I have an SQL table containing fields with date and time with columns start and end as DateTime.

What I want is a query that will select the row where the current date and time is between the value of start and end fields plus the type is WE.

Example:

If current date and time is 2022-05-18 15:30:00, row number 39 should be displayed as a result.

So far, this is the code I came up with but it returns zero result:

select * from `examdates` where (NOW() between `start` and `end`) and `type`='WE'

I also tried some answers I found in this forum like

select * from `examdates` where NOW() >= `start` and NOW() <=`end` and `type`='WE'

but result is still

Thanks!

Answer 1

Test this:

SELECT * 
FROM `examdates` 
WHERE CONVERT_TZ(NOW(), @@time_zone, '+00:00') BETWEEN `start` AND `end`
  and `type`='WE'

The function retrieves server timezone info from session variable and converts the server local datetime to GMT time zone. If the values in the table are ones of some another timesone then adjust 3rd function parameter accordingly.

See https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b054129c8210336d70b6f0a4ecc50b5d