date field

[jeff5656]

I originally set up a date filed as a varchar.  However, when I sorted it (i.e "ORDER BY rcf_date DESC"

 

I notice it treats it like alphnumeric.  in other words 03/12/08 comes before 3/1/08 because of the leading zero.

 

However if I go into phpmyadmin and change it from varchar to date, it screws up the data already there ( i.e. 0000-00-00).

 

So how can I make that field be treated like a date so that 3/12/08 is the same as 03/12/08, and hopefully a method that does mnot screw up the existing data.

 

Funnily, if I import the data to excel, excel correctly sees it as a date and I can sort it correctly!

[Zhadus]

The best way to do it would be to change the information in your database. I would suggest writing a script to pull all the date values from your database and converting them to the correct format and updating the DB.

[jeff5656]

Ok I will try to figure out how to do that.

 

However, people write the date as 2/15/08 so it is a shame that there is no way to display dates in that format.  It is quite strange that with php, thge only way to display dates is 2008-02-15.

 

Are there no webpages out there that use databases that display dates at 02/15/08??  Seems hard to believe.

 

When I changed the field from varchar to date and entered 02/15/08, it was converted to 0000-00-00!!!

[Zhadus]

What you can do is if you are taking input as mm/dd/yy, you can parse it into the 0000-00-00 format for saving in the database. Then when you draw from database, you can format it into the mm/dd/yy format actually using PHP. For the PHP portion, take a look at date() and see the formatting capabilities.

[benphp]

This format 2/15/08 is not a good way to save dates in the database for several reasons. Once you get the dates into 0000-00-00 format, you can use something like this to display them:

 

list($startyearprint, $startmonthprint, $startdayprint) = split('[/.-]', $start);
$startDate = "$startmonthprint/$startdayprint/$startyearprint";

 

and this to encode them for the database:

 

$start = "5/9/2008";
list($smonth, $sday, $syear) = split('[/.-]', $start);
$startDate = "$syear-$smonth-$sday";

 

[Zhadus]

Also, the easiest way I think to resave the data in your database would be to use the following code.

 

$query = mysql_query("SELECT * FROM table")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
$id[] = $info['id'];
$date[] = $info['date'];
}
for ($x = 0; $x < sizeof($date); $x++) {
$newDate = split("/", $date[$x]);
$day = $newDate[0];
$month = $newDate[1];
$year = $newDate[2];
if ($year > 50) {
$year = "19" . $year;
} else {
$year = "20" . $year;
}
$date[$x] = $year . "-" . $month . "-" . $day;
}

 

That isn't 100% tested, but I think you can see the general idea. I'm also sure there's an easier way to do it.

[jeff5656]

Thanks.  Now obviously I would have to run that script BEFORE I change the field from varchar to DATE (because do so changes everything into 0000-00-00) right?

But then the question is, does that code work with a varchar since you are assuming it is a date.

 

One small addition to complicate things:  some of the fields have "999999" and some people even wrote "weekend" under date.  I know, it's a mess.  Also I have 930 records so the stakes are kind of high.

[Zhadus]

I'm not sure if changing from varchar to Date will set it to 0000-00-00 if it's actually in that format. If so, create a new column in your database, labeled like date2 and insert the values according to ID to that field and then just delete the first and rename date2 to date.

 

As far as strange data like "999999" and "weekend," you'll need to add to the code I provided to change those into junk values and turn them into 0000-00-00 or whatever you see fit there. Otherwise, how did you intend to order your database like that in the first place?

 

Think of this as a friendly reminder to plan for the future in the beginning, and to always format user input

[benphp]

Changing varchar to date should be the last thing you do, after you've reformatted all the dates. I'd pull out the records into a backup just in case.

 

The code will work with varchar if it's formatted in 0000-00-00 format. Also, I don't see how you're going to get around manually checking the new dates. The fact that you have all sorts of chars in the date field is one of the reasons why it's good to convert it to a standard before saving in to the db.

 

Unless you're a wizard at regular expressions, you're going to have to check all the new dates. I believe, someone correct me if I'm wrong, that if you convert the varchar date field to date, and there's something like "dunno" in it, MySQL is kind enough to enter 0000-00-00 in for you. But if there's 999999, it won't be so kind, and you're likely to end up with 9999-99-00.

 

Creating a new date2 field, as suggested by Zhadus, is a good idea too.

[jeff5656]

Thanks I'll try the extra column suggestion.  The reason I have these problems is that all this data was opriginally entered into an excel spreadsheet and peopke would type in "weekend" or whatever sometimes.  Then I had to save to CSV and then import into SQL.  That is somewhat ugly for many reasons.  If there was an option in excel to save as sql, I could do all this manipulation in excel and then export it with the correct format....I guess microsoft would never purposely play nice with open source things like sql!!

[benphp]

You could export it into excel, change it, then export it to csv and use:

 

LOAD DATA INFILE 'c:/temp/mydata.txt'

INTO TABLE mytable

FIELDS TERMINATED BY '|'

LINES TERMINATED BY '\n';

 

I've done that with about 1000 records.