CODEWITHSUNDEEP
phpmysqlsqlstringdate
zippax
zippax

Reputation: 324

php select records between 2 dates with formatted date column

I need to select orders' records between 2 dates. the date format on the database is looks like : January 1, 2020 -> using %F %j, %Y format.

my try to get the records is:

$from_date = 'January 1, 2020';
$to_date = 'January 5, 2020';

$sql = "
SELECT * 
  FROM orders 
 WHERE order_date STR_TO_DATE(order_date, '%F %j, %Y') between STR_TO_DATE('$from_date', '%F %j, %Y') and STR_TO_DATE('$to_date', '%F %j, %Y') 
 GROUP 
    BY order_id
";

the result is null

also I tried to use convert(order_date,'%F %j, %Y') but not work and shows me the same result null

hope I explain the issue clearly.

Thanks

Upvotes: 0

Views: 45

Answers (1)

GMB
GMB

Reputation: 222432

There is no '%F' specifier in MySQL. You probably want %M' instead.

Also, '%j' should be '%e': the latter gives you the number of a day in the month (from 1 to 31), while the former is the number of a day in the year (from 1 to 365 - or 366 on leap years).

Your where clause should look like:

where str_to_date(order_date, '%M %e, %Y') 
    between str_to_date(:from_date, '%M %e, %Y') and str_to_date(:to_date, '%M %e, %Y')

Notes:

  • Use prepared statements! Do not concatenate variables in the query string: this is both inefficient (the database needs to parse the statement everytime it is executed with different parameters) and unsafe (your code is opened to SQL injection)

  • You should really consider using the proper datatype to store your data; storing dates as strings is inefficient (you need to convert the strings whenever you need to filter them) and unsafe (you cannot guarantee data integrity at the time when the data is stored in the table)

  • select * and group by do not go along well together

Upvotes: 2

Related Questions