In my order table, i have a "delivery_date" field with varchar type and all values look like this : "d/m/y at H:m"
How can i convert this field and values to a valid datetime Y-m-d H:i:s ?
3
-
You should have to give datatype for your field to datetime. And before that you can convert your all existing dates to valid date format "Y-m-d H:i:s"Rahul Patel– Rahul Patel2016-07-30 18:09:09 +00:00Commented Jul 30, 2016 at 18:09
-
Ok and how can i handle conversion with STR_TO_DATE ?AniZ– AniZ2016-07-30 18:12:16 +00:00Commented Jul 30, 2016 at 18:12
-
DATE_FORMAT(STR_TO_DATE(delivery_date, '%d/%m/%y HH:MM'), '%Y-%m-%d HH:MM:SS')Rahul Patel– Rahul Patel2016-07-30 18:14:12 +00:00Commented Jul 30, 2016 at 18:14
Add a comment
|
5 Answers
Update your existing dates by following query.
update table_name set delivery_date = DATE_FORMAT(STR_TO_DATE(delivery_date, '%d/%m/%y HH:MM'), '%Y-%m-%d HH:MM:SS');
After your all dates has been modified by above query, you can change data type to datetime for delivery_date. And make change in your code accordingly for insering new records, updating existing records and related functionality as such we are changing date format.
Comments
SELECT STR_TO_DATE(delivery_date, '%d/%m/%Y at %H:%i') as date_value
FROM order_table
See the format here
Comments
Since you dont have seconds in your column you will get 00 as second always.
SELECT
DATE_FORMAT(STR_TO_DATE(delivery_date, '%d/%m/%y at %H:%i'),'%Y-%m-%d %H:%i:%s') as dt
FROM your_table;
Note:
But I would suggest convert all your string values to valid timestamp by an update query. And later change the data type to timestamp.
These are the steps to be followed:
UPDATE your_table SET delivery_date =
DATE_FORMAT(STR_TO_DATE(delivery_date, '%d/%m/%y at %H:%i'),'%Y-%m-%d %H:%i:%s');
ALTER TABLE your_table MODIFY delivery_date timestamp;
Comments
You can use MySQL's STR_TO_DATE function or you could use PHP's strtotime funciton.
strtotime(date('Y-m-d H:i:s'));
Comments
use this build-in method from MySQL
DATE_FORMAT(NOW(), '%d %m %Y') AS your_date;
--
DATE_FORMAT('2022-06-12', '%d %m %Y') AS your_date;
Comments
