date conversion

[Space Cowboy]

Hey guys, I need a bit of help here with dates.

 

Im rebuilding a website (a CMS). Ive been given the existing database which I now need to input into the new CMS.

 

The dates (of the articles) are in this format "18/02/2006 16:32:57"

 

Im used to working with dates in this format "20061802163257" (from this format its easy to order (the articles) by dates etc).

How can I convert this date format (on the fly) to something more workable?

[per1os]

www.php.net/strtotime

 

www.php.net/date

 

Should help ya.

[Space Cowboy]

thanks..... but that website was the first place I looked!!! But it doesnt answer my question.

 

I already have the date in the awkward format. I need to convert this into the traditional "20060522123353" format.

[Barand]

Add a column, say, "newdate" TYPE DATETIME, to the table

 

In this query below, substitute your date column name for "dmy" and run the query

UPDATE dates SET
newdate = CONCAT(SUBSTRING(dmy,7,4),'-', SUBSTRING(dmy,4,2), '-', SUBSTRING(dmy,1,2), ' ', SUBSTRING(dmy, 12,)

 

Drop existing date column. Rename the newdate column.

[Space Cowboy]

thanks, I dont fully understand that query but I will give it a go!

 

...presuming this quesry understands this daft format the date it currently in? (ie 18/02/2007 16:40:53"). Infact ideally I could do to get rid of the time completly, and just convert to "20071802"

[Barand]

then make newdate column  type DATE and use

 

UPDATE tablename SET

newdate = CONCAT(SUBSTRING(dmy,7,4),'-', SUBSTRING(dmy,4,2), '-', SUBSTRING(dmy,1,2)

[Space Cowboy]

ive done this and it doesnt work.

 

and from looking at that query, it doesnt mention anywhere the old colum or date. So how does this query know what date it needs to convert?

[per1os]

thanks..... but that website was the first place I looked!!! But it doesnt answer my question.

 

I already have the date in the awkward format. I need to convert this into the traditional "20060522123353" format.

 

Are you sure?

 

<?php
$date = "05/22/2006 12:33:53";

$date = strtotime($date);
$new_date = date('Ymdhis', $date);

echo $new_date;
?>

[Barand]

and from looking at that query, it doesnt mention anywhere the old colum or date. So how does this query know what date it needs to convert?

 

In this query below, substitute your date column name for "dmy" and run the query