[thelist] RE: Long Date to Datetime Conversion

Alan McCoy a.r.mccoy at larc.nasa.gov
Thu Dec 5 14:03:13 CST 2002


I think I've figured it out...almost.

The following code almost works, however, it changes the date for all of
them to the date of the last record.

<<begin code>>

$db = mysql_connect("localhost", "user", "password");

mysql_select_db("mydb",$db);

$result = mysql_query("SELECT * FROM mystories",$db);

while ($myrow = mysql_fetch_row($result)) {

# Get long-formatted date (i.e., September 13, 2001) from record
$date = $myrow[17];

# Convert long date to UNIX timestamp format
$date = strtotime($date);

# Convert UNIX timestamp format to MySQL datetime format (yyyy-mm-dd)
$date = strftime("%Y-%m-%d",$date);

# Update each record with new date format
$sql = "UPDATE mystories SET time='$date'";

# Not too sure about this line...
$newresult = mysql_query($sql);

# Display each result
printf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s<hr>\n",
$myrow[0], $myrow[1], $myrow[2], $myrow[3], $myrow[4], $myrow[5],
$myrow[6], $myrow[7], $myrow[8], $myrow[9], $myrow[10], $myrow[11],
$myrow[12], $myrow[13], $myrow[14], $myrow[15], $myrow[16], $myrow[17]);

}

mysql_close();

<<end code>>

The orginal formatted dates are in $myrow[17] and the column where the
newly converted dates should be updated to is $myrow[4] (which is named
"time" in the db and is datetime formatted).

Anyone out there happen to see a glitch in this?

Thanks again in advance!

Alan


:: -----Original Message-----
:: From: Alan McCoy [mailto:a.r.mccoy at larc.nasa.gov]
:: Sent: Wednesday, December 04, 2002 3:22 PM
:: To: 'thelist at lists.evolt.org'
:: Subject: Long Date to Datetime Conversion
::
::
:: I have a sql dump file of a bunch of news articles that I
:: need to import into a MySQL database. However, the dates on
:: all the records is in long format (like "October 13, 1998")
:: and the database field that the dates will be entered into
:: is in datetime format. Normally, I would change the date
:: column to varchar, but there's already a BUNCH of records
:: already there.
::
:: Is there a painless method of converting the date format so
:: that "October 13, 1998" would become "1998-11-13" ?
::
:: Thanks in advance!
::
:: Alan
::




More information about the thelist mailing list