date conversion becomes 0000-00-00

[jeff5656]

I am using the following code to convert the date in a varchar field to the accepted format for a DATE field.

I put the converted format into "rcf_date2":

 

 

However the original date (i.e.04/17/08) now reads "0000-00-00".

 

Any ideas?

 

If I keep rcf_date2 as a varchar insted of DATE, it reads as 2008-17-04 but as soon as I convert the field type to date, it becomes 0000-00-00.

[Zhadus]

Create a new column in your DB as DATE datatype and insert the values from rcf_date2 into the new column.

[jeff5656]

I did.  actually rcf_date is the original and I convert that and put that into rcf_date2 with the result being 0000-00-00.

Here's code (sorry should have put in code in first post):

 

<?php include "connectdb.php"; 


function convert_date($dt) {
     list($d,$m,$y) = split("/", $dt);
     $y = ($y > 50 && $y < 100)?'19' . $y: '20' . $y;
     return($y . "-" . $m . "-" . $d);
}

$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
$upq = "update ph_consults set rcf_date2 = '" . convert_date($info['rcf_date']) . "' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());
}
?>

[craygo]

why not just use date function with strotime if your dates are already 4/14/2008

 

$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
$newdate = date("Y-m-d", strtotime($info['rcf_date']));
$upq = "update ph_consults set rcf_date2 = '$date' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());

 

Ray

[jeff5656]

set rcf_date2 = '$date'

 

did you mean set rcf_date2 = '$newdate'?

 

I didn't see where simply "$date" was defined and my field name is called rcf_date (or rcf_date2)

[craygo]

LOL crap sorry

 

yes I meant $newdate

 

Ray

[jeff5656]

Thanks that works perfectly.  Now, let's say there are some fields that are NOT in that format.  Like "9999" or "wknd"

 

What bit of code could I write so that it would convert these fields to 0000-00-00?

And where would I put this code into here:

 

<?php include "connectdb.php"; 


$query = mysql_query("SELECT * FROM ph_consults")or die(mysql_error());
while($info = mysql_fetch_array( $query ))
{
$newdate = date("Y-m-d", strtotime($info['rcf_date']));
$upq = "update ph_consults set rcf_date = '$newdate' where id_incr = '" . $info['id_incr'] . "'";
   $rs = mysql_query($upq) or die("Problem with the update query: $upq<br>" . mysql_error());
   
}
?>

[Zhadus]

Those will automatically be formatted to 1970-01-01.