CODEWITHSUNDEEP
mysql
user3057670
user3057670

Reputation: 3

Retrieve records between two dates in mysql?

My query is 

select *
from emp 
where DATE_FORMAT(approved_date,'%d-%m-%Y')BETWEEN '01-01-2014' and '26-01-2014';

But it displays the records in the year of 2013.

Table is approved date:

01-10-2013
01-01-2014
25-2-2014

Upvotes: 0

Views: 70

Answers (3)

Amarnath Balasubramanian
Amarnath Balasubramanian

Reputation: 9460

MySQL STR_TO_DATE() function

dd-mm-yyyy format

select * from emp 
         where approved_date > STR_TO_DATE('2014-01-01','%d-%m-%Y') and 
               approved_date < STR_TO_DATE('2014-01-25','%d-%m-%Y');

You can also use Between Too

   select * from emp 
             where approved_date BETWEEN STR_TO_DATE('2014-01-01','%d-%m-%Y')  
                   AND STR_TO_DATE('2014-01-25','%d-%m-%Y');

MySQL STR_TO_DATE Function - Features, Examples and Equivalents

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

select * from emp 
where approved_date 
BETWEEN STR_TO_DATE('18-01-2014', '%d-%m-%Y') 
AND STR_TO_DATE('26-01-2014', '%d-%m-%Y')

Working Demo: http://sqlfiddle.com/#!2/b4d7e/13

Upvotes: 3

Sai Avinash
Sai Avinash

Reputation: 4753

A Sample Example:

SELECT users.* from users 
WHERE created_at >= '2011-12-01' 
AND created_at <= date_add('2011-12-01', INTERVAL 7 DAY)Which will select all users in the same interval You might also find the BETWEEN operator more readable:

or

SELECT users.* from users 
WHERE created_at BETWEEN('2011-12-01', date_add('2011-12-01', INTERVAL 7 DAY));

Hope this helps..

Upvotes: 0

Related Questions