Page 1 of 1

date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:14 am
by jonnyfortis
I have a date being passed back from a payment gateway

the variable is
$TRXDATE
this has a return value of MM/DD/YY or 03/02/15

when this is stored in the database it is storing it as 2003-02-15 when it should be 2015-02-03

i have tried
$newDate = date('d/m/Y',strtotime($TRXDATE));
the using $newDate to update but the results are coming back as 0000-00-00

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:23 am
by Celauran
jonnyfortis wrote: this has a return value of MM/DD/YY or 03/02/15

when this is stored in the database it is storing it as 2003-02-15 when it should be 2015-02-03

i have tried
$newDate = date('d/m/Y',strtotime($TRXDATE));
This is all inconsistent. Is it February 3 or March 2? DateTime will parse slashes as MM/DD/YY, so you could use that directly if that's indeed the format you're working with. You'd do best to store your dates as YYYY-MM-DD in the DB and format them however you please when you're displaying them.

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:30 am
by jonnyfortis
Celauran wrote:
jonnyfortis wrote: this has a return value of MM/DD/YY or 03/02/15

when this is stored in the database it is storing it as 2003-02-15 when it should be 2015-02-03

i have tried
$newDate = date('d/m/Y',strtotime($TRXDATE));
This is all inconsistent. Is it February 3 or March 2? DateTime will parse slashes as MM/DD/YY, so you could use that directly if that's indeed the format you're working with. You'd do best to store your dates as YYYY-MM-DD in the DB and format them however you please when you're displaying them.
its march 2nd.
the DB is set to date and should store dates as 0000-00-00

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:32 am
by Celauran
So use DateTime::format to convert them.

Code: Select all

$date = new DateTime('03/02/15');
echo $date->format('Y-m-d');

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:38 am
by jonnyfortis
Celauran wrote:So use DateTime::format to convert them.

Code: Select all

$date = new DateTime('03/02/15');
echo $date->format('Y-m-d');
i have tried

$newDate = new DateTime($TRXDATE);

then used the $newDate to update the DB but am getting the following error

Catchable fatal error: Object of class DateTime could not be converted to string in /usr/../../cancel.php on line 185

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:51 am
by Celauran
jonnyfortis wrote:$newDate = new DateTime($TRXDATE);

then used the $newDate to update the DB
No, you need to format the date as I showed above. You can't just shove an object into the DB's date field.

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:57 am
by jonnyfortis
Celauran wrote:
jonnyfortis wrote:$newDate = new DateTime($TRXDATE);

then used the $newDate to update the DB
No, you need to format the date as I showed above. You can't just shove an object into the DB's date field.
but how do i use the variable that is returned?

$TRXDATE

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 7:59 am
by Celauran
Exactly as you have been. Pass it to the DateTime constructor. All you need to change is to format the date before inserting it in the DB.

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 8:09 am
by jonnyfortis
Celauran wrote:Exactly as you have been. Pass it to the DateTime constructor. All you need to change is to format the date before inserting it in the DB.
ok i get that
i have tried
$newDate = $TRXDATE;
$date = new DateTime($newDate);
i tried to use the $date to update the DB
but i dont want to echo out at this point.

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 8:10 am
by Celauran
So don't echo it. I don't understand what the problem is. What does your insert code look like now?

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 8:15 am
by jonnyfortis
Celauran wrote:So don't echo it. I don't understand what the problem is. What does your insert code look like now?

Code: Select all

 $newDate = $TRXDATE;
 $date = new DateTime($newDate);

if($STATUS == 1){
  $query="UPDATE host_payments2014 SET payment_transaction_status='CANCELLED', payment_amount_paid='".$AMOUNT."', payment_paid_timestamp='".$date."' WHERE payment_id='".$ORDERID."'";
  }

$result=mysql_query($query)
or die(mysql_error());
this give me an error

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 8:17 am
by Celauran

Code: Select all

 $newDate = $TRXDATE;
 $date = new DateTime($newDate);

if($STATUS == 1){
  $query="UPDATE host_payments2014 SET payment_transaction_status='CANCELLED', payment_amount_paid='".$AMOUNT."', payment_paid_timestamp='".$date->format('Y-m-d')."' WHERE payment_id='".$ORDERID."'";
  }

$result=mysql_query($query)
or die(mysql_error());

Re: date being stored incorrectly in database

Posted: Mon Mar 02, 2015 8:22 am
by jonnyfortis
thanks for that....it was staring me in the face


Celauran wrote:

Code: Select all

 $newDate = $TRXDATE;
 $date = new DateTime($newDate);

if($STATUS == 1){
  $query="UPDATE host_payments2014 SET payment_transaction_status='CANCELLED', payment_amount_paid='".$AMOUNT."', payment_paid_timestamp='".$date->format('Y-m-d')."' WHERE payment_id='".$ORDERID."'";
  }

$result=mysql_query($query)
or die(mysql_error());