Originally Posted by x2002ugp

Interesting.

Hey Mike, sorry to raise this thread from the dead, but did either you or mramsey figure out what this issue was?

I'm getting the same error:

Code:

1292: Incorrect datetime value: '2010-06-02T23:20:54.000Z' for column 'CreatedDate' at row 1

I'm using your code from this post:
http://www.mikesimonds.com/synchroni...1.html#post906

With the the latest adodb-511-for-php5, and the PHPToolKit from your homepage.

It's been almost two years since this post was first made, but I'm hoping you can remember what the resolution was.

Thanks again for such an awesome site!

UPDATE:

I found a crappy work-around. I've changed these date fields to be VARCHAR(30) in MySQL, and the export works fine.

I then added a short bit of code at the end of the script to update other MySQL fields with the proper DATETIME values:

PHP Code:

$soql = "Select Id, IsDeleted, MasterRecordId, Name, ParentId, BillingStreet, BillingCity, BillingState, BillingPostalCode, BillingCountry, Phone, Fax, Website, OwnerId, CreatedDate, CreatedById, LastModifiedDate, LastModifiedById, SystemModstamp, LastActivityDate, Primary_Category__c, Do_Not_Call__c, Do_Not_Fax__c, Additional_Categories__c, Additional_Market_Areas__c, County__c, NOLO_Lead_Source__c, Lead_Source_Detail__c, Do_Not_E_Mail__c, Telesales_Agent__c, Primary_Market_Area__c, pymt__Convert_To_Person_Account__c, Code__c, Phone_2__c, Notes__c, Taking_new_Clients__c, Old_SF_Record_ID__c, Add_l_Info__c, Number_of_Contacts__c FROM Account";

//Processes the query to get account information from Salesforce
$records = get_records($client, $soql, $db);

// Here I update two of my "custom" fields in MySQL (DateCreated and DateLastModified):
$sql = "UPDATE $table SET DateCreated = LEFT(CreatedDate,23), DateLastModified = LEFT(LastModifiedDate,23)";

$db->Execute($sql);
if ($db)
{
    echo "dates updated\n";
}
else
{
    echo "Failed";
    var_dump($db->ErrorMsg());
    exit();
}


exit; 


This is what I do to dates that i am inserting into the database:

Let's say that you are extracting the CreatedDate from Salesforce, from any object:

PHP Code:

$records = get_records($client, $soql);
 //echo '<pre>' . print_r($records, true) . '</pre>';
foreach ($records as $r)
{
   /* if you echo this out, it will render 2008-09-23T15:57:43.000Z to the screen*/ 
   echo $r->fields->CreatedDate . '<br />';

   $test['CreatedDate']  = $r->fields->CreatedDate;
}


$date = date("Y-m-d", strtotime($test['CreatedDate']));
echo $date ; 


look at the line

PHP Code:

$date = date("Y-m-d", strtotime($test['CreatedDate'])); 


That echo's out:

Code:

2008-09-23

here is the whole test script that I wrote:

PHP Code:

<?php
ini_set("soap.wsdl_cache_enabled","0"); 
error_reporting(E_ALL);


require_once ('/users/msimonds/public_html/includes/sfdc.inc');



$soql = "Select Id, CreatedDate FROM Design_Registration__c";
$records = get_records($client, $soql);
 //echo '<pre>' . print_r($records, true) . '</pre>';
foreach ($records as $r)
{
    echo $r->fields->CreatedDate . '<br />';
   $test['CreatedDate']  = $r->fields->CreatedDate;
}

//$date = strtotime($test['CreatedDate']);
$date = date("Y-m-d", strtotime($test['CreatedDate']));
echo $date ;
exit;


function get_records(&$connection, &$query)
{
    $queryOptions = new QueryOptions(2000);

    $response = new QueryResult($connection->query($query));
    // if the size is zero, where done
    if ($response->size > 0)
    {
        $products = $response->records;
        // Cycles through additional responses if the number of records
        // exceeds the batch size
        while (!$response->done)
        {
            set_time_limit(100);
            $response = $connection->queryMore($response->queryLocator);
            $products = array_merge($products, $response->records);
        }
    }
    return $products;
}


 
 
  
?>

Hope this helps, I am going on Vacation and will not be around to respond after today


best of luck!!

~Mike

Since I already know that the string format of the CreatedDate is '2010-06-02T23:20:54.000Z', doing that debug isn't neccessarily helpful.

I'm wondering if it's possible to modify the value of that CreatedDate inside of that "foreach" loop before we pass the values to the store_in_db function?

That way I could pass MySQL a date format that it understands.

I think I just need to drop the last character for MySQL to properly understand it as a DATETIME?

Enjoy your vacation!

couldn't you just store it into a string, then str_replace that character ?

Not sure, but that may work

~Mike