CODEWITHSUNDEEP
mysql
Joao Victor
Joao Victor

Reputation: 1171

Trouble with date format when comparing two dates in mysql

I have a datetime column that i'm trying to compare with a date value in the DD/MM/YYYY format.

I'm trying this:

SELECT * FROM TABLE WHERE STR_TO_DATE(DATETIME_COLUMN, '%d/%m/%Y') = '04/04/2014'.

It doesn't work.

Even tried this:

SELECT * FROM TABLE WHERE DATETIME_COLUMN = '04/04/2014'

Which seems to be working on the rest of my code, but it doesn't.

Is there another function?

Upvotes: 0

Views: 134

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

You can achieve your goal using this query which will keep you predicate sargable.

SELECT * FROM TABLE WHERE DATETIME_COLUMN = STR_TO_DATE('04/04/2014', '%d/%m/%Y')

if your DATETIME_COLUMN column has time part and you want to get all dates within given date, use this:

SELECT * FROM TABLE 
WHERE DATETIME_COLUMN >= STR_TO_DATE('04/04/2014', '%d/%m/%Y')
  AND DATETIME_COLUMN < STR_TO_DATE('05/04/2014', '%d/%m/%Y')

Upvotes: 1

PravinS
PravinS

Reputation: 2584

Use like this

SELECT * FROM TABLE WHERE DATE_FORMAT(DATETIME_COLUMN,'%d/%m/%Y') = '04/04/2014';

Upvotes: 0

Related Questions