mysql query of a date, between two date fields

2019-02-23 08:51发布

I'm using MySql 5.5.

I need to find a userid on a date with a particular ip address.

The fields are userid, ipaddress, startdate, enddate.

So for instance I am looking for a userid with ip address 192.168.1.1 on Sep 12 2011.

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

Any help to pointing out this logic flaw is welcome. Thank you.

3条回答
SAY GOODBYE
2楼-- · 2019-02-23 09:47

the obvious solution would be like:

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

but theres a shortcut using BETWEEN so you can simply write:

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

note: BETWEEN also works for numbers, strings and other stuff, and it's possible to negate it by writing NOT BETWEEN - quite useful sometimes.

查看更多
霸刀☆藐视天下
3楼-- · 2019-02-23 09:50

It's not very clear if you want:

'2011-12-09' BETWEEN startdate AND enddate

or:

'2011-12-09' = startdate AND '2011-12-09' = enddate
查看更多
姐就是有狂的资本
4楼-- · 2019-02-23 09:54

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

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

Or as pointed out by ypercube you can use BETWEEN:

SELECT * FROM table WHERE ipaddress = '192.168.1.1' AND '2011-12-09' BETWEEN startdate AND enddate;
查看更多
登录 后发表回答