Forum Moderators: coopster
ALTER TABLE table MODIFY mydatecolumn DATE;However, as dreamcatcher recommended, and I concur, always, always backup your file prior to mass modifications so you have something to fall back on in case things don't work out as planned.
$sql = "select date_column_name from table_name";
$qryrslt = mysql_query($sql);
while( $row = mysql_fetch_array($qryrslt,MYSQL_ASSOC) ) {
$tmpdate = $row["date_column_name"];
// Perform whatever conversion you need here on the
// $tmpdate variable.
$sql = "insert into table_name (new_date_column_name) values ($tmpdate);"
mysql_query($sql);
}
This assumes you have a connection to the db established.
Back up the database first.
For a do-able example, like a "December 14, 2004" type format, In your while loop through all the $row['old_date'] results, use a regex to catch anything starting with a spelled out month (or abreviation), followed by a comma or space or both, followed by one or two numbers (day of month), followed by a comma or space or both, and ending with a two to four didgets (year). Later, strtotime() [us2.php.net] should accept about anything the regex does, except for a misspelled month name or abreviation:
if (preg_match('!^([^\d, ]+[, ]{1,2}\d{1,2}[, ]{1,2}\d{2,4}$!i'),$row['old_date']){
echo $row['old_date']." is a match.<br>\n";
if (!$new_date = date('Y-m-d',strtotime($row['old_date']))) {
echo "The month must be misspelled<br>\n";
}
else echo "\$new_date = $new_date<br>\n";
}
else {
echo $row['old_date']." is not a match.<br>\n";
/* Add as many elseifs and regexs for different old_date formats.
Most other formats, I'm thinking you can explode on a / or the like
and concatenate the rearranged elements to get ccyy-mm-dd */
} I'm getting better at this stuff, because I tested this, and I actually got my regex right the first time! (I don't think that's ever happened before. :)
$date_elements = explode('/',$row['old_date']);
$new_date = $date_elements[2]."-".$date_elements[0]."-".$date_elements[1];
ALTER TABLE table ADD COLUMN newdate DATE;
UPDATE table SET newdate=CONCAT_WS('-', SUBSTRING(olddate,7,4), SUBSTRING(olddate,1,2), SUBSTRING(olddate,4,2));
(which is essentially the same as the above PHP, just done entirely in the database.)
if you're satisified with the contents of newdate, you can drop olddate and rename newdate to olddate.
I would write a quick script to validate the data in your file to make sure they are all valid dates in that format.
<pre>
<?php
$sql = "SELECT mydate FROM mytable";
// execute your database query
while ($row = fetch from the result set) {
$month = substr [php.net]($row['mydate'], 0, 2);
$day = substr($row['mydate'], 3, 2);
$year = substr($row['mydate'], 6, 4);
if (!checkdate [php.net]($month, $day, $year)) {
print "Bad one: " . $row['mydate'] . "\n";
}
}
?>
</pre>
--
-- ADD the new column:
--
ALTER TABLE table ADD mynewdate DATE AFTER mydate;
--
-- Here is where you can get fancy, depending on your database.
-- I'll use MySQL examples:
--
-- MySQL >= 4.1.1
--
UPDATE table SET mynewdate = STR_TO_DATE(mydate, '%m/%d/%Y');
--
-- MySQL < 4.1.1
--
UPDATE table
SET mynewdate = CONCAT_WS('-',
SUBSTRING(mydate, 7, 4),
SUBSTRING(mydate, 1, 2),
SUBSTRING(mydate, 4, 2))
;
--
-- Drop the original column:
--
ALTER TABLE table DROP mydate;
--
-- Rename the new column of new type DATE to the original name:
--
ALTER TABLE table CHANGE mynewdate mydate DATE;
<pre>
<?php $sql = "SELECT DOPEN FROM _clientsw_copy";
// execute your database query
while ($row = fetch from the result set) { $month = substr($row['DOPEN'], 0, 2);
$day = substr($row['DOPEN'], 3, 2); $year = substr($row['DOPEN'], 6, 4);
if (!checkdate($month, $day, $year)) { print "Bad one: " . $row['DOPEN'] . "\n"; } }?>
</pre>
Thanks to all of you who responded to my post! This is one of the best forums I have participated in! All of you! Have an Awesome Day!
// execute your database query
while ($row = fetch from the result set)
These were just comments because I did not know which database you were using. You need to replace them with the actual functions corresponding to the database. For example, if it was MySQL you need to first connect to your database, then execute the script.
<pre>
<?php
$connection = mysql_connect('localhost', 'username', 'password')
or exit('Could not connect (' . mysql_errno() . '): ' . mysql_error());
$db = mysql_select_db('databasename', $connection)
or exit('Could not select database (' . mysql_errno() . '): ' . mysql_error());
$sql = "SELECT DOPEN FROM _clientsw_copy";
// execute your database query
$rows = mysql_query [php.net]($sql);
while ($row = mysql_fetch_array [php.net]($rows)) {
$month = substr($row['DOPEN'], 0, 2);
$day = substr($row['DOPEN'], 3, 2);
$year = substr($row['DOPEN'], 6, 4);
if (!checkdate($month, $day, $year)) {
print "Bad one: " . $row['DOPEN'] . "\n";
}
}
?>
</pre>
