CODEWITHSUNDEEP
mysqldatedate-format
user1749399
user1749399

Reputation: 93

Selecting date range MySQL with date_format

I've got an issue selecting a date range with MySQL.

SELECT MvtDate,date_format(MvtDate,'%d-%m-%Y')
  FROM (`immmvt`)
 WHERE date_format(MvtDate,'%d-%m-%Y') BETWEEN '01-01-2010' AND '02-01-2010'

mvtDate type is date like 2010-01-01 00:00:00.

When I run the query, the result works for the days and the months but it also show me other result from other years.

Like 01-01-2011 etc.

Upvotes: 9

Views: 18419

Answers (2)

John Woo
John Woo

Reputation: 263703

You should use STR_TO_DATE since you want to convert string back to date

SELECT MvtDate, date_format(MvtDate,'%d-%m-%Y')
FROM  `immmvt`
WHERE MvtDate BETWEEN STR_TO_DATE('01-01-2010','%d-%m-%Y') AND 
                      STR_TO_DATE('02-01-2010','%d-%m-%Y')

FYI: DATE_FORMAT() converts date to formatted string representation.
     STR_TO_DATE() converts formatted string back to date

Upvotes: 13

Salil
Salil

Reputation: 47472

SELECT MvtDate,date_format(MvtDate,'%d-%m-%Y')
  FROM (`immmvt`)
 WHERE date_format(MvtDate,'%d-%m-%Y') IN ('01-01-2010', '02-01-2010')

Upvotes: 3

Related Questions