CODEWITHSUNDEEP
mysqlsql
NullVoxPopuli
NullVoxPopuli

Reputation: 65173

How do I query between two dates using MySQL?

The following query:

SELECT * FROM `objects` 
WHERE (date_field BETWEEN '2010-09-29 10:15:55' AND '2010-01-30 14:15:55')

returns nothing.

I should have more than enough data to for the query to work though. What am I doing wrong?

Upvotes: 349

Views: 1025150

Answers (12)

Daniel Vandersluis
Daniel Vandersluis

Reputation: 94264

Your second date is before your first date (ie. you are querying between September 29 2010 and January 30 2010). Try reversing the order of the dates:

SELECT *
FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

Official Docs: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

Upvotes: 633

Bang Andre
Bang Andre

Reputation: 537

I just tested on mariaDB with 2 cases as follows:

Case 1: SELECT * FROM table_name WHERE DATE(date_field) BETWEEN '2016-12-01' AND '2016-12-10' // include 2016-12-10
Case 2: SELECT * FROM table_name WHERE (date_field BETWEEN '2016-12-01' AND '2016-12-10') // not include 2016-12-10

Upvotes: 5

Jodyshop
Jodyshop

Reputation: 664

To display post(s) between 2 specific dates (for example):

an occasion starts on (04-12) and ends on (04-14) without selecting a year in query to make it recurrent every year on the specified dates, So my goal is to display that occasion on startdate and hide it automatically on enddate as follow:

$stmt = $db->query(
    "SELECT * FROM table

    WHERE (CAST(CURDATE() AS date)

    BETWEEN

    CAST(table.date_start AS date)
    AND
    CAST(table.date_end AS date))

    LIMIT 1"
);

Now, the occasion starts and disappear between these specified dates only, not after or before.

Upvotes: -1

Bruno Barral
Bruno Barral

Reputation: 67

When using Date and Time values, you must cast the fields as DateTime and not Date. Try :

SELECT * FROM `objects` 
WHERE (CAST(date_field AS DATETIME) 
BETWEEN CAST('2010-09-29 10:15:55' AS DATETIME) AND CAST('2010-01-30 14:15:55' AS DATETIME))

Upvotes: 3

DS Mercy
DS Mercy

Reputation: 41

Just Cast date_field as date 

SELECT * FROM `objects` 
WHERE (cast(date_field as date) BETWEEN '2010-09-29' AND 
'2010-01-30' )

Upvotes: 4

Fahd Allebdi
Fahd Allebdi

Reputation: 414

You can do it manually, by comparing with greater than or equal and less than or equal.

 select * from table_name where created_at_column  >=   lower_date  and  created_at_column <= upper_date;

In our example, we need to retrieve data from a particular day to day. We will compare from the beginning of the day to the latest second in another day.

  select * from table_name where created_at_column  >=   '2018-09-01 00:00:00'  and  created_at_column <= '2018-09-05 23:59:59';

Upvotes: 11

ltlBeBoy
ltlBeBoy

Reputation: 1312

As extension to the answer from @sabin and a hint if one wants to compare the date part only (without the time):

If the field to compare is from type datetime and only dates are specified for comparison, then these dates are internally converted to datetime values. This means that the following query

SELECT * FROM `objects` WHERE (date_time_field BETWEEN '2010-01-30' AND '2010-09-29')

will be converted to 

SELECT * FROM `objects` WHERE (date_time_field BETWEEN '2010-01-30 00:00:00' AND '2010-09-29 00:00:00')

internally.

This in turn leads to a result that does not include the objects from 2010-09-29 with a time value greater than 00:00:00!

Thus, if all objects with date 2010-09-29 should be included too, the field to compare has to be converted to a date:

SELECT * FROM `objects` WHERE (DATE(date_time_field) BETWEEN '2010-01-30' AND '2010-09-29')

Upvotes: 22

sabin
sabin

Reputation: 901

DATE() is a MySQL function that extracts only the date part of a date or date/time expression

SELECT * FROM table_name WHERE DATE(date_field) BETWEEN '2016-12-01' AND '2016-12-10';

Upvotes: 35

Jure1873
Jure1873

Reputation: 899

Try switching the dates around:

2010-09-29 > 2010-01-30?

Upvotes: -1

Nik
Nik

Reputation: 4075

Your query should have date as 

select * from table between `lowerdate` and `upperdate`

try

SELECT * FROM `objects` 
WHERE  (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

Upvotes: 36

SoulWanderer
SoulWanderer

Reputation: 305

Might be a problem with date configuration on server side or on client side. I've found this to be a common problem on multiple databases when the host is configured in spanish, french or whatever... that could affect the format dd/mm/yyyy or mm/dd/yyyy.

Upvotes: 1

gen_Eric
gen_Eric

Reputation: 227310

Is date_field of type datetime? Also you need to put the eariler date first.

It should be:

SELECT * FROM `objects` 
WHERE  (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')

Upvotes: 27

Related Questions