MySQL SELECT rows In a Date Range

pty 882 Posting Pro · 2006-08-22T15:11:12+00:00

Hello,
I was wondering if there anyway to Select rows where the date column falls into a certain range?
For example, heres a table
The date field is generated automatically by MySQL with the DATE function

Heres the table:

[user] . [wins].  [date]
bobby.......4..... 2006-08-21
boby1.......3..... 2006-08-22
boby2.......6..... 2006-08-24
boby3.......1..... 2006-08-29
Now I need to filter this data out so that I can have the end user browse weekly results. I'm stuck on a SELECT syntax statement that can pull any rows from any dates ranging from 8-21 to 8-27 (Monday to Sunday this month).
If this was the case, the last row , boby3 who has 1 wins on 08-29 will not be displayed for this current week's result.
I already have this which ranks users OVERALL season wins:
SELECT user, sum(wins) from results GROUP BY user ORDER by wins
Does anyone know any referrences regarding this issue?
Thanks for reading this,
Bobby

select *
from table
where date >= [start date] and date <= [end date]

or (i'm not 100% sure if this works in mysql but it does in postgres!)

select * 
from table 
where date between [start date] and [end date]

bobby08 0 Newbie Poster · 2008-09-21T13:37:31+00:00

Both seem to work beautifully!

Thanks!

lenel2 0 Newbie Poster · 2008-12-11T22:16:44+00:00

Hi, thanks for the info...

SELECT *
FROM directorydata
WHERE RegDate >= '2008-10-10' AND RegDate <= '2008-12-11'

this works fine but let say i want to substitute the from and to dates, from 2 text boxes.

e.g. (<input type="text" name="from" id="from" />
and <input type="text" name="to" id="to" /> )

i know you can use "LIKE variables" to get data from a form... i just want to set my own date ranges

it200219 0 Light Poster · 2010-06-08T02:08:38+00:00

Hello all,

Please make sure if you use timestamp as column type and using `between` in SQL query its advisable to use h:m:s format dates

i.e

BETWEEN '2010-05-31 00:00:00' AND '2010-06-06 23:59:59'

Please advise your comments.

red_ruewei 0 Light Poster · 2011-11-15T08:43:25+00:00

Hi all,thanks for example.It help me.
I need a little help here.
I'm stuck on how to SELECT rows In a Month Range. The month is take from date yyyy-mm-dd stored on database.

I manage to select record on particular month and year using this below example.

Select * from table where YEAR(date_data)='2011' AND MONTH(date_data)='01'

But have no idea how to select record in month range of year.

Thanks in advance

pty 882 Posting Pro · 2011-11-15T19:28:42+00:00

SELECT * FROM table WHERE YEAR(date_data)= 2011 AND MONTH(date_data) BETWEEN 1 AND 4;

Or preferably:

SELECT * FROM table WHERE date_data BETWEEN '2011-01-01' AND '2011-02-01';

red_ruewei 0 Light Poster · 2011-11-17T07:47:51+00:00

SELECT * FROM table WHERE YEAR(date_data)= 2011 AND MONTH(date_data) BETWEEN 1 AND 4;

Or preferably:

SELECT * FROM table WHERE date_data BETWEEN '2011-01-01' AND '2011-02-01';

thanks for reply.
I try something like this to read record with range month and year.
Example from June, 2011 to June 2012

Select * from table where (YEAR(date1)>='$year1' AND MONTH(date1)>='$mon1') and (YEAR(TKH_date1)<='$year2' AND MONTH(TKH_date1)<='$month2')

i had test this. And run separately.

(YEAR(date1)>='$year1' AND MONTH(date1)>='$mon1')

For above code it run smoothly

(YEAR(TKH_date1)<='$year2' AND MONTH(TKH_date1)<='$month2')

But for this code, it fail....

TQ

pty 882 Posting Pro · 2011-11-23T20:48:03+00:00

thanks for reply.
I try something like this to read record with range month and year.
Example from June, 2011 to June 2012
Select * from table where (YEAR(date1)>='$year1' AND MONTH(date1)>='$mon1') and (YEAR(TKH_date1)<='$year2' AND MONTH(TKH_date1)<='$month2')
i had test this. And run separately.
(YEAR(date1)>='$year1' AND MONTH(date1)>='$mon1')
For above code it run smoothly
(YEAR(TKH_date1)<='$year2' AND MONTH(TKH_date1)<='$month2')
But for this code, it fail....
TQ

Fails why? What error? What variables? Also, by calling YEAR(blah) you're going to bypass your indices and your query will be slow. Something like this is better and will work.

SELECT * FROM table WHERE date_data >= '2011-06-01' AND date_data < '2011-07-01';

red_ruewei 0 Light Poster · 2011-12-05T06:50:06+00:00

i'm sorry, its not fail but the records display is incorrect...

pty 882 Posting Pro · 2011-12-05T15:20:06+00:00

i'm sorry, its not fail but the records display is incorrect...

Incorrect? Incorrect how? Also, this is my last post on this topic, you clearly are either a troll or an idiot.

red_ruewei 0 Light Poster · 2011-12-28T12:52:16+00:00

Incorrect? Incorrect how? Also, this is my last post on this topic, you clearly are either a troll or an idiot.

I'm sorry if annoying you.
What i mean the record is incorrect when it also display date 0000-00-00 when run that sql statement.

But i manage to solve this problem by referring to your example.

Here is the solution

SELECT * FROM table WHERE YEAR(date1) BETWEEN $year1 AND $year2 and MONTH(date1) BETWEEN $month1 and $month2

The issue here happen because i also need range of year. Like from Jan 2011 to Jan 2012.Sorry for my bad explanation.
I dont know why it also view '0000-00-00' date record. May be is true i'm idiot.

It ok if you dont want to post on this topic.

Really appreciate you attention for idiot person like me.
Thank you

Lavish Dubey 0 Newbie Poster · 2013-01-18T06:35:01+00:00

tanxs bro....its working....