Between Date Range

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
luketheduck
Forum Newbie
Posts: 18
Joined: Mon Apr 19, 2004 9:13 am
Location: Aylesbury, Bucks, UK

Between Date Range

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?
User avatar
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'
User avatar
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'
User avatar
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.
Post Reply