Page 1 of 1

Between Date Range

Posted: Tue May 30, 2006 8:32 am
by luketheduck
This is probably a simple fix for someone who knows how, I just can't figure it out right now.

I have a news table with dates in mysql, and, for example am attempting to search for all news posted in January 2004.

My query contains:

Code: Select all

WHERE 'date' BETWEEN '2004-01-01' AND '2004-01-31'
Only, it doesn't display any news posted on the 1st or the 31st.

How do I go about amending this?

Posted: Tue May 30, 2006 8:47 am
by jayshields

Code: Select all

WHERE `date` BETWEEN '2003-12-31' AND '2004-02-01'
I can't say I've ever used a BETWEEN clause with dates, but I've used > and <, they work as imagined so you could easily change it to

Code: Select all

WHERE `date` >= '2004-01-01' AND `date` <= '2004-01-31'

Posted: Tue May 30, 2006 9:40 am
by luketheduck
Yes that worked nicely, cheers.

Posted: Wed May 31, 2006 1:09 am
by jmut
I would do

Code: Select all

WHERE 'date' BETWEEN '2004-01-01 00:00:00' AND '2004-01-31 23:59:59'
If `date` is a datetime type column.
And without the hours thing if just date column.

Posted: Wed May 31, 2006 4:38 am
by timvw
[quote="jmut"]I would do

Code: Select all

WHERE 'date' BETWEEN '2004-01-01 00:00:00' AND '2004-01-31 23:59:59'
This would 'miss' all the events on '2004-01-31 23:59:59'.



a BETWEEN b AND c is (in most databases) the equivalent of b <= a < c.

Posted: Wed May 31, 2006 6:26 am
by jmut
timvw wrote:
jmut wrote:I would do

Code: Select all

WHERE 'date' BETWEEN '2004-01-01 00:00:00' AND '2004-01-31 23:59:59'
This would 'miss' all the events on '2004-01-31 23:59:59'.



a BETWEEN b AND c is (in most databases) the equivalent of b <= a < c.
well...in mysql it's not I guess. It will include the events on '2004-01-31 23:59:59'

Posted: Wed May 31, 2006 10:04 am
by pickle
This might be easiest:

Code: Select all

WHERE
  DATE_FORMAT(date,'%Y%m') == '200601'
FYI - If you're displaying sql syntax, you can use the [_syntax_="sql"][/_syntax_] tags (without the underscores)