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)