MySQL Select * from TABLE Between DATE RANGE?

Posted on 2009-02-08
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
    LVL 26

    Expert Comment

    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

    LVL 2

    Author Comment

    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

    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

    LVL 2

    Author Closing Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Suggested Solutions

    This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    This video discusses moving either the default database or any database to a new volume.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now