CODEWITHSUNDEEP
javamysqlsqlsearchselect
David Gidony
David Gidony

Reputation: 1283

MySql Select Query is not acting properly

I want to search in a Date column.

I use this query:

SELECT * FROM requestserverdb.closedrequests 
WHERE
ClosingTimeStamp >= '2014-09-04' 
AND 
ClosingTimeStamp <= '2014-09-19';

I only get results from 04-09-2014 to 18-09-2014 , it does not include the last day !

also if I use:

SELECT * FROM requestserverdb.closedrequests 
WHERE
ClosingTimeStamp >= '2014-09-04' 
AND 
ClosingTimeStamp <= '2014-09-04';

I will not get any results at all !

how come ?

what am I doing wrong ?

its just ignores the '=' sign...

please help :)

Dave.

Upvotes: 0

Views: 65

Answers (3)

ekad
ekad

Reputation: 14614

It's because '2014-09-19' means 19 September 2014 at midnight (00:00:00), so the query won't return any records with ClosingTimeStamp equals 2014-09-19 09:25:00 for example. You should add one day to the upper bound of the second where condition and make it < instead of <= to avoid the query from returning any record with ClosingTimeStamp equals 2014-09-20 00:00:00

SELECT * FROM requestserverdb.closedrequests 
WHERE
ClosingTimeStamp >= '2014-09-04' 
AND 
ClosingTimeStamp < '2014-09-20';

Upvotes: 1

Chris Franklin
Chris Franklin

Reputation: 167

Another option for solving this is to add "23:59:59" to the end of the date. This would make things easier in automatically generated queries than bothering with advancing the date one day. Plus if you set it ahead a day and you actually have a closed request with the timestamp of '2014-09-20 00:00:00' that WILL be included in the results. Which I would assume you do not want. You could do this instead.

SELECT * FROM requestserverdb.closedrequests 
WHERE
ClosingTimeStamp >= '2014-09-04' 
AND 
ClosingTimeStamp <= '2014-09-19 23:59:59';

Upvotes: 2

Jens
Jens

Reputation: 69440

Try to convert your string to DateTime with time part:

SELECT * FROM requestserverdb.closedrequests 
WHERE
ClosingTimeStamp between STR_TO_DATE('2014-09-04 0:0:0' , '%Y/%c/%d %H:%i:%s')) AND 
STR_TO_DATE('2014-09-19 23:59:59' , '%Y/%c/%d %H:%i:%s'))

Upvotes: 0

Related Questions