date being stored incorrectly in database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

date being stored incorrectly in database

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date being stored incorrectly in database

Post 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.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date being stored incorrectly in database

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date being stored incorrectly in database

Post by Celauran »

So use DateTime::format to convert them.

Code: Select all

$date = new DateTime('03/02/15');
echo $date->format('Y-m-d');
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date being stored incorrectly in database

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date being stored incorrectly in database

Post 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.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date being stored incorrectly in database

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date being stored incorrectly in database

Post 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.
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date being stored incorrectly in database

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date being stored incorrectly in database

Post by Celauran »

So don't echo it. I don't understand what the problem is. What does your insert code look like now?
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date being stored incorrectly in database

Post 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
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: date being stored incorrectly in database

Post 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());
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: date being stored incorrectly in database

Post 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());
Post Reply