Darshan Darshan - 3 months ago 9
MySQL Question

MYSQL less Than fuction not working properly

I need to get all records those equal and less than 2014-08-31 and greater than 2014-08-01 i used bellow query for this, does anyone has idea ?

SELECT * FROM table where user_id = 322 and createdDate > '2014-08-01' and createdDate <= '2014-08-31';


Table Value

createdDate
2014-08-01 05:14:52
2014-08-27 05:15:08
2014-08-29 02:54:06
2014-08-30 05:57:12
2014-08-31 07:56:13


Here my output was

2014-08-01 05:14:52
2014-08-27 05:15:08
2014-08-29 02:54:06
2014-08-30 05:57:12


i can not get 2014-08-31 07:56:13 this value.
Please anybody know why is this happening ?

Answer

The problem is that '2014-08-31' is interpreted as '2014-08-31 00:00:00'

The quickest fix would be:

SELECT * FROM table where user_id = 322 and createdDate > '2014-08-01' and createdDate < '2014-09-01';

This would list all before 1st September.

The correct solution would be to use dates instead of strings.