CODEWITHSUNDEEP
mysqlsqldateselect
TPSstar
TPSstar

Reputation: 121

How to search through date_format field in MySQL

I have changed the date format of field, however its not finding the record on new date pattern. 

SELECT id, DATE_FORMAT(sale_date, "%d-%m-%Y") AS sale_date 
FROM sales 
WHERE sale_date = '31-12-2012';

Upvotes: 4

Views: 1687

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT id, DATE_FORMAT(sale_date, "%d-%m-%Y") AS sale_date 
FROM sales 
WHERE sale_date = STR_TO_DATE('31-12-2012' , "%d-%m-%Y") ;

OR

SELECT id, DATE_FORMAT(sale_date, "%d-%m-%Y") AS sale_date 
FROM sales 
WHERE DATE_FORMAT(sale_date, "%d-%m-%Y") = '31-12-2012';

Upvotes: 3

Ray
Ray

Reputation: 41448

Assuming you're using a timestamp or date format for your column sale_date, you must specify the date like YYYY-MM-DD:

    WHERE sale_date = '2012-12-31';

Upvotes: 1

Related Questions