CODEWITHSUNDEEP
phpmysql
user2307052
user2307052

Reputation: 29

how to get data between two date with two different year?

My query below doesn't seem to work.

SELECT * FROM `test` WHERE date between '12/30/2013' and '01/05/2014'

But when I change the order of dates like in this query below, it seems to be working

SELECT * FROM `test` WHERE date between '01/01/2014' and '01/05/2014'

What is the correct way to use date ranges in SELECTs?

Upvotes: 2

Views: 792

Answers (3)

Fabien Ménager
Fabien Ménager

Reputation: 140205

It won't work because the dates are not ISO 8601 formatted: "2013-12-30" for example.

The BETWEEN clause makes a string comparaison, so you need either to use a correct date format in your database, or format the dates with DATE_FORMAT() or STR_TO_DATE(str,format).

Edit: Try this query, which will work if you store dates as strings formatted as %m/%d/%Y, which is a bad idead, MySQL has a built-in DATE format :

SELECT * FROM test where STR_TO_DATE(date, '%m/%d/%Y') between STR_TO_DATE('01/01/2014', '%m/%d/%Y') and STR_TO_DATE('01/05/2014','%m/%d/%Y');

The DATE_FORMAT MySQL takes a DATE or DATETIME value as first argument, which you don't use, that's why it didn't work (in addition to the '$' you used instead of '%' before 'Y')

Upvotes: 3

Deepika Janiyani
Deepika Janiyani

Reputation: 1477

Use php and sql together to get the result as below

$date1 = date("Y-m-d", strtotime('12/30/2013'));

$date2 = date("Y-m-d", strtotime('01/05/2014'));

$sql    =   "SELECT * FROM `test` WHERE date between '".$date1."' 
                     and '".$date2."'";

Upvotes: 1

deW1
deW1

Reputation: 5660

select * from test where date between "2013-12-30" and "2014-01-05"

This will work fine

Upvotes: 0

Related Questions