Hello. Newbie asking for help on converting date formats. I have a working uery that will convert a date format from mm/dd/yyyy to yyyy-mm-dd. I need to be able to convert to yyyy-mm-dd the variations that occur when the months and days are single digits and the year is 2 digits (m/dd/yyyy, m/d/yyyy, mm/d/yyyy, m/d/yy, mm/d/yy, m/dd/yy, m/d/yy, etc.). Here is the code I have so far:

PHP Code:

mysql_query("UPDATE compsin SET date = CONCAT(SUBSTRING(date, 7, 4),'-',SUBSTRING(date, 1, 2),'-',SUBSTRING(date, 4, 2))"); 


Thanks in advance.

I would convert the date string to a timestamp and then use date() to format it however I want

PHP Code:

$date = 'mm/dd/yyyy';
$timestamp = strtotime($date);

$newDate = date('n/j/y' , $timestamp); // m/d/yy 


http://us.php.net/manual/en/function.date.php
http://us.php.net/manual/en/function.strtotime.php

So it doesn't matter what format the date has in $date? It could be m/d/yy, or, mm/d/yyyy, mm/dd/yy, etc.? Then after converting $date to a timestamp, I can use DATE() to put it into the yyyy-mm-dd format? Thanks. I'll give it a try. I am using my query to convert all the dates in the date column of my database to yyyy-mm-dd. How would I include your code into a query? Looks like I might have to loop through the database or could this be done without looping?

Originally Posted by gdaniels

So it doesn't matter what format the date has in $date? It could be m/d/yy, or, mm/d/yyyy, mm/dd/yy, etc.?

I believe the function assumes that the date is formatted as month/day/year with any number of digits. Double check the documentation if you have problems with it.

Originally Posted by gdaniels

Then after converting $date to a timestamp, I can use DATE() to put it into the yyyy-mm-dd format? Thanks. I'll give it a try.

Yes. Check the documentation for more information on formatting.

Originally Posted by gdaniels

I am using my query to convert all the dates in the date column of my database to yyyy-mm-dd. How would I include your code into a query? Looks like I might have to loop through the database or could this be done without looping?

If you want to do this in SQL only take a look at the date functions:
http://dev.mysql.com/doc/refman/5.1/...functions.html

I don't know how off the top of my head.

As you can see from these examples, the strtotime function can take an extremely wide range of formats. It's a pretty awesome function

http://us3.php.net/strtotime#functio...otime.examples