CODEWITHSUNDEEP
mysql
Jaz
Jaz

Reputation: 55

Cannot scan the right values of Order date in sql

I want to fetch all datas that corresponds in the chosen date range. So the problem is that. When theres included time in the data. It can't fetch the required data to be displayed. But when I remove the time on it. It displays really well. What can I do to make it right?

EXAMPLE VALUES:

  1. 2018-10-29 01:21:29pm

  2. 2018-10-30 01:21:29pm

EXAMPLE VALUES THAT WORKS:

  1. 2018-10-29

  2. 2018-10-30

My query:

 `"SELECT *,SUBSTRING(order_date,1,10) from orders where order_date >='$fromdate' AND order_date <='$todate'"`

Upvotes: 0

Views: 42

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28854

Ideal Solution: You will need to change the datatype of order_date from Varchar(500) to Datetime type, using Alter Table command.

Now, it is noteworthy that the MySQL datetime value is in YYYY-MM-DD HH:MM:SS format. So firstly, you will need to change your datetime string to MySQL datetime format string. Otherwise, directly changing the datatype will lead to irreparable loss/truncation of data.

Your datetime value 2018-10-29 01:21:29pm is basically of YYYY-MM-DD HH:MM:SS AM/PM (12 hour format). In terms of format specifiers, it would be: '%Y-%m-%d %h:%i:%s%p'. Complete list of available format specifiers can be seen in MySQL docs.

Firstly, we use Str_To_Date() function to convert all your data into proper Datetime format.

UPDATE orders 
SET order_date = STR_TO_DATE(order_date, '%Y-%m-%d %h:%i:%s%p');

Now, next step is simple. Just modify the datatype to datetime:

ALTER TABLE orders 
MODIFY COLUMN order_date datetime;

Upvotes: 1

Related Questions