Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
-
luketheduck
- Forum Newbie
- Posts: 18
- Joined: Mon Apr 19, 2004 9:13 am
- Location: Aylesbury, Bucks, UK
Post
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?
-
jayshields
- DevNet Resident
- Posts: 1912
- Joined: Mon Aug 22, 2005 12:11 pm
- Location: Leeds/Manchester, England
Post
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'
-
luketheduck
- Forum Newbie
- Posts: 18
- Joined: Mon Apr 19, 2004 9:13 am
- Location: Aylesbury, Bucks, UK
Post
by luketheduck »
Yes that worked nicely, cheers.
-
jmut
- Forum Regular
- Posts: 945
- Joined: Tue Jul 05, 2005 3:54 am
- Location: Sofia, Bulgaria
-
Contact:
Post
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.
-
timvw
- DevNet Master
- Posts: 4897
- Joined: Mon Jan 19, 2004 11:11 pm
- Location: Leuven, Belgium
Post
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.
-
jmut
- Forum Regular
- Posts: 945
- Joined: Tue Jul 05, 2005 3:54 am
- Location: Sofia, Bulgaria
-
Contact:
Post
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'
-
pickle
- Briney Mod
- Posts: 6445
- Joined: Mon Jan 19, 2004 6:11 pm
- Location: 53.01N x 112.48W
-
Contact:
Post
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)
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.