MySQL Select * from TABLE Between DATE RANGE?

Posted on 2009-02-08
Medium Priority
Last Modified: 2012-05-06
Hey Guys,

I would like to know how to structure and SQL query to query between months ranges

Something along the lines of:
SELECT * FROM table WHERE datetime BETWEEN xx/xx/xx AND xx/xx/xx

We have a table with the following date field entry named datetime:


Which I am assuming is an entry for 2009 02 08 TIME SECONDS or something along those lines

I have read the MySQL Reference on "date" but couldn't make much sense of it - SQL isn't my strong point.

Thanks in advance.
Question by:illuzian
  • 2
  • 2
LVL 26

Expert Comment

ID: 23583436
Try this..
SELECT * FROM table WHERE STR_TO_DATE(`datetime`,'%Y%m%d%f') between '2009-02-01' AND '2009-02-28';
You can change the daterange 2009-02-01 and 2009-02-28... 

Open in new window


Author Comment

ID: 23583783
Okay cheers for the reply but we are using mysql 3.23 so it looks like this is not an options

I have put together this query but it only works if I specify

= 'xxxx-xx-xx'

and not the between as shown bellow any ideas?
select * from saled where date_add(concat(SUBSTRING(`DATETIME` FROM 1 FOR 4),'-',SUBSTRING(`DATETIME` FROM 5 FOR 2),'-',SUBSTRING(`DATETIME` FROM 7 FOR 2)), interval 0 day) between '2008-03-01' and '2008-06-01';

Open in new window

LVL 26

Accepted Solution

Umesh earned 2000 total points
ID: 23583856
This way you can do this..
select * 
  from saled 
where unix_timestamp('20080408175726478') 
BETWEEN UNIX_TIMESTAMP('2008-03-01') and UNIX_TIMESTAMP('2008-06-01');

Open in new window


Author Closing Comment

ID: 31544190
Excellent answer for the query, I should of specified the version in the initial post as STR_TO_DATE was not available.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question