Update date field

[Suchy]

I need to change the date field in my db from this format:

06, 08, 2007, 11:40:24

to

06/08/2007, 11:40:24

 

How can I form this query ??

 

[Barand]

Both of your formats are totally useless as db date columns.

 

Change to 2007-06-08 11:40:24 and change column type to DATETIME

 

Then you can compare dates, sort by dates, query by date or date ranges, use Mysql date/time functions

 

NOTE: I've assumed 06 is the month and 08 is the day as you don't specify and there is no location in your profile (if it's the other way round you'll have to adjust the fomat string below) You will also need to provide your tablename and date_column name in the queries.

 

Backup your table first (in case it doesn't work, although it did in my test)

 

To change the format

UPDATE tablename SET date_col = STR_TO_DATE(date_col, '%m, %d, %Y, %H:%i:%s')

 

then change col type

ALTER TABLE `tablename` MODIFY COLUMN `date_col` DATETIME;

 

 

[Suchy]

Sorry that I did not specify the details, but the field is actualy a varchar field into which a date is entered when someone uploads something.

........
putenv("TZ=Europe/Warsaw");
$date_upload = date("d, m, Y, G:i:s");
.......

[revraz]

Even still, if you want to do any type of sorting on it, you should still change it.  It's easy to display it anyway you like, but you should store it as an actual date format.

[fenway]

Sorry that I did not specify the details, but the field is actualy a varchar field into which a date is entered when someone uploads something.

Yes, that the problem..no varchar, use a datetime field.