CODEWITHSUNDEEP
phpmysql
user3436467
user3436467

Reputation: 1775

mysql date range query for particular date format (MMM-YY)

I have a date column in mysql db but the dates are formatted like so:

Is it possible to query a date range using this date format ?

For example:

$query = "SELECT `Total` FROM agent WHERE date >= 'Jan-16' AND date < Apr-16";

If this is not valid, what is the recommended way to move forward? will i have to update the date format in the db ?

Upvotes: 0

Views: 126

Answers (1)

Blank
Blank

Reputation: 12378

  • Jan-16
  • Feb-16
  • Mar-16

If this column is char or varchar or other string type, and you just compare these to your parameter, it will not give what you want cause this only compare them with a character's ASCII code. You should use function str_to_date to format them, then you can get result in a date interval.

Like this;)

$query = "SELECT `Total` FROM agent
WHERE str_to_date(date, '%M-%y') >= str_to_date('Jan-16', '%M-%y')
AND str_to_date(date, '%M-%y') < str_to_date('Apr-16', '%M-%y');

and if your parameter is date, just use it. Also you can update your column to date, it will be a better solution.

Upvotes: 1

Related Questions