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