mysql query of a date, between two date fields

Question

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.

`AND '2011-12-09' BETWEEN startdate AND enddate` – ypercubeᵀᴹ Feb 01 '12 at 14:51 — ypercubeᵀᴹ, Feb 01 '12 at 14:51

score 4 · Accepted Answer · answered Feb 01 '12 at 14:53

4

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

answered Feb 01 '12 at 14:53

ypercubeᵀᴹ

note that `between` does a `<=`/`>=`-comparision, so doing `=` on start- and enddate might not give the exact same result in every case. – oezi Feb 01 '12 at 15:08
Apologies, I want the first one ''2011-12-09' BETWEEN startdate AND enddate' – notmyname Feb 01 '12 at 15:20

oezi · Answer 2 · 2012-02-01T15:04:52.943

1

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.

edited Feb 01 '12 at 15:04

answered Feb 01 '12 at 14:54

oezi

thanks for the hint, ypercube - i'm not a native english speaker and so this is what happens if you're too lazy to look up a word... but now it's correct. – oezi Feb 01 '12 at 15:06

score 0 · Answer 3 · answered Feb 01 '12 at 14:53

0

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;

answered Feb 01 '12 at 14:53

mauris

formatting the querys would make this much easier to read, having to scroll horizontally is annoying ;) – oezi Feb 01 '12 at 14:59

3 Answers3