selecting data from table based on date .

Question

i have database table like this

+-------+--------------+----------+
|   id  |    ip        |    date  |
+-------+--------------+----------+
|   505 |192.168.100.1 |2010-04-03|
|   252 |192.168.100.5 |2010-03-03|
|   426 |192.168.100.6 |2010-03-03|
|   201 |192.168.100.7 |2010-04-03|
|   211 |192.168.100.10|2010-04-03|
+-------+--------------+----------+

how can i retirive data from this table where month=03 how to write sql to do that .

select * from table where month=03 

something like that .

Answer 1

If using mysql, use the MONTH() function I think.

select * from table where month(date) = 3

Answer 2

Alternatively

select * from table where '2010-02-31' < date < '2010-04-01' 

should work.

Answer 3

select * from table where month(date) = 3

If you are searching over 2010-03, it would be recommended to do it this way:

select * from table where date >= '2010-03-01' AND date < '2010-04-01'

Because this query will use an index if available

Answer 4

SELECT * FROM Table WHERE month(date) = 3

OR using the datepart Function:

SELECT * FROM Table WHERE datepart(mm, date)=3