Mysql date request, between two date fields

Question

I am using MySql 5.5.

I need to find the user id on a date with a specific IP address.

Fields: userid, ipaddress, startdate, enddate.

So, for example, I'm looking for a user ID with ip address 192.168.1.1 dated September 12, 2011.

the request will be similar to select * from database where ipaddress='192.168.1.1' and 2011-12-09 is in(startdate and enddate);

Any help aimed at identifying this logical error is welcome. Thanks.

Answer 1

Not very clear if you want:

 '2011-12-09' BETWEEN startdate AND enddate 

or

 '2011-12-09' = startdate AND '2011-12-09' = enddate 

Answer 2

an obvious solution would look like this:

  [...] AND startdate <= '2011-12-09' AND enddate >= '2011-12-09' 

but theres shortcut using BETWEEN so you can just write:

  [...] AND '2011-12-09' BETWEEN startdate AND enddate 

note: BETWEEN also works for numbers, strings, and other things, and this can be undone by writing NOT BETWEEN - quite useful sometimes.

Answer 3

Since there is a start and end date, perhaps something along the line:

 SELECT * FROM table WHERE ipaddress = '192.168.1.1' AND '2011-12-09' >= startdate AND '2001-12-09' <= enddate; 

Or, as indicated by ypercube, you can use BETWEEN :

 SELECT * FROM table WHERE ipaddress = '192.168.1.1' AND '2011-12-09' BETWEEN startdate AND enddate;