I have a mySQL with a table. There are 30 records all with a date column.
How do I change all my existing records in my table to have today's date with is format?
date_default_timezone_set('America/Los_Angeles');
$date = date("m/d/y g:i A") ;
You can use str_to_date to convert a date string to MySQL's internal date format for inserting.
Change the curdate() (current date) format in MySQL The current date format is 'YYYY-mm-dd'. To change current date format, you can use date_format().
We change the date format from one format to another. For example - we have stored date in MM-DD-YYYY format in a variable, and we want to change it to DD-MM-YYYY format. We can achieve this conversion by using strtotime() and date() function.
Here's the fix for the VARCHAR to DATETIME (this will erease the current value):
ALTER TABLE mytable modify column `mycolumn` datetime NOT NULL DEFAULT 0;
UPDATE mytable SET mycolumn = NOW() WHERE ...;
or
UPDATE mytable SET mycolumn = '2011-09-25 17:40:00' WHERE ...;
If you want to save the current value use:
ALTER TABLE mytable add column `newdate` datetime NOT NULL DEFAULT 0;
UPDATE mytable SET newdate = mycolumn;
ALTER TABLE mytable DROP COLUMN mycolumn;
If you want to select the date in the format you can:
SELECT DATE_FORMAT(mycolumn, '%m/%e/%y %h:%i %p') FROM mytable WHERE ...
Or in your PHP you can use:
date_default_timezone_set('America/Los_Angeles');
// query select ($row = mysql_fetch_assoc($query)...
$date = $date = date("m/d/y g:i A", strtotime($row['mycolumn']));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With