CODEWITHSUNDEEP
mysqlsql
sivakumar V
sivakumar V

Reputation: 135

Unable to use between clause with date in MySQL

Here is my MySQL query.

SELECT Timestamp,a_number,Verdict,OrderId 
FROM Main 
WHERE Timestamp between DATE '2014-03-12' AND '2014-03-11' 
and a_location like '%london%';

In above query Timestamp is one of the field in Main table. But when i execute the query it is executing and getting the output as empty set.

When I checked Timestamp values that stored in database i am getting the values that are given below

1323517427743,1323867674980.

What is the mistake that i have done. And what should i make change to get my need.

Upvotes: 0

Views: 73

Answers (3)

xdazz
xdazz

Reputation: 160833

If the Timestamp's column type is timestamp, then you could just do:

SELECT `Timestamp`, a_number,Verdict,OrderId 
FROM Main 
WHERE `Timestamp` between '2014-03-11' AND '2014-03-12' 
and a_location like '%london%';

If it is integer, then you should do:

SELECT `Timestamp`, a_number,Verdict,OrderId 
FROM Main 
WHERE `Timestamp` between UNIX_TIMESTAMP('2014-03-11') AND UNIX_TIMESTAMP('2014-03-12') 
and a_location like '%london%';

And for between A and B, A should less or equal than B, or no result will be returned.

Upvotes: 0

Mureinik
Mureinik

Reputation: 311028

col BETWEEN a AND b is a syntactic sugar over col >= a AND col <= n - i.e., you must use the correct order:

SELECT Timestamp,a_number,Verdict,OrderId 
FROM   Main 
WHERE  Timestamp between DATE '2014-03-11' AND '2014-03-12' 
AND    a_location like '%london%';

Upvotes: 2

ajtrichards
ajtrichards

Reputation: 30565

Try using back ticks around the DATE column

SELECT Timestamp,a_number,Verdict,OrderId 
FROM Main 
WHERE Timestamp between `DATE` '2014-03-12' AND '2014-03-11' 
and a_location like '%london%';

Upvotes: 0

Related Questions