CODEWITHSUNDEEP
mysql
Jasmine
Jasmine

Reputation: 1

how to query date field between two days in mySQL

I know how to do it in Oracle, but I can't find same function in mySQL. try to google it..but can't find it.

I have a table, there is a field name "create_date" , attribute is datetime so field value format looks like "2009-09-25 11:48:24"

Now I want to add date condition , I want to find create_date between 2009/09/01 to 2009/10/14 my sql command: where 1=1 and date_format(create_date,'%d/%m/%Y') BETWEEN '01/09/2009' AND '14/10/2009'

it's OK..I can get rowdata but when I change begin date to 2009/09/30, end date is same

date_format(create_date,'%d/%m/%Y') BETWEEN '30/09/2009' AND '14/10/2009'

and no result... but if I change begin date to 2009/10/01 , I can get result again. I jsut want to know what happend? it should fine to

Upvotes: 0

Views: 524

Answers (1)

dcrosta
dcrosta

Reputation: 26258

I think you're using a function (DATE_FORMAT) on the wrong thing: you should STR_TO_DATE your parameters, and make MySQL do a date range comparison, rather than converting the row value to a string and doing a string range comparison (I'm not even sure that makes sense or works in all cases). Try this:

SELECT ...
  FROM my_table t
 WHERE t.create_date BETWEEN STR_TO_DATE('2009/09/01', '%Y/%m/%d')
                         AND STR_TO_DATE('2009/10/01', '%Y/%m/%d')

I think that would work better.

Upvotes: 1

Related Questions