Select item from database based on date range

Question

Hi all,

I have a database of events, and I need to be able to show an event occuring 'today'. The events have start and end dates in the format: Y-m-d h:i:s

How would I do this? I've tried queries along the lines of:

SELECT * 
FROM tbl_events 
WHERE start>='2009-11-03 16:00:00' 
and end<='2009-11-03 16:00:00'

To no avail. Any advice would be greatly appreciated!

Answer 1

If you only care about the dates and not the times, this will work for 'today'

select * from tbl_events where start >= curdate() and end < curdate() + 1

If you want to specify the dates and times, this will work:

select * from tbl_events 
where start >= '2009-11-03 08:00:00' and end <= '2009-11-03 16:00:00'

Answer 2

You want to select events that occurred on this day, not at this precise second, right? Hence your WHERE clause should look more like start = '2009-11-03'.

Answer 3

The reason it may now work, could be that end is a restricted word (or something of that sort)

mysql.com begin-end info page

you may want to change your database field names to be event_start and event_end, which will hopefully fix the problem, though I could be talking rubbish.

Also if it helps at all you can use the following method to get dates from a range

SELECT * FROM tbl_event WHERE event_start BETWEEN (start_date) AND (end_date)

This only looks at one field, in this case event_start, but means that if you want to get range of events which have a start date between a set range you can.

Answer 4

Have you tried casting the datetime string to type "datetime" ?

SELECT 
    * 
FROM 
    tbl_events 
WHERE 
        start >= CAST('2009-11-03 16:00:00' AS DATETIME)
    AND end   <= CAST('2009-11-03 16:00:00' AS DATETIME)

Or, for that matter, what's wrong with using a function like NOW() or a keyword like CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_DATE?

For Exmaple:

SELECT
    *
FROM
    tbl_events
WHERE
        start >= CURRENT_TIMESTAMP
    AND end   <= CURRENT_TIMESTAMP

EDIT: Andy-Score may be on to something with the between operator and reserved words, something like:

SELECT
    *
FROM
    tbl_events
WHERE
        CURRENT_TIMESTAMP BETWEEN `start` AND `end`

Answer 5

select count(*) as ce from estudantes where nascimento between DATE_SUB(CURDATE( ),INTERVAL 18 YEAR) and DATE_SUB(CURDATE( ),INTERVAL 25 YEAR)