date being stored incorrectly in database
Moderator: General Moderators
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
date being stored incorrectly in database
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
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
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 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));
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: date being stored incorrectly in database
its march 2nd.Celauran wrote: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 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));
the DB is set to date and should store dates as 0000-00-00
Re: date being stored incorrectly in database
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
i have triedCelauran wrote:So use DateTime::format to convert them.
Code: Select all
$date = new DateTime('03/02/15'); echo $date->format('Y-m-d');
$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
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 wrote:$newDate = new DateTime($TRXDATE);
then used the $newDate to update the DB
-
jonnyfortis
- Forum Contributor
- Posts: 462
- Joined: Tue Jan 10, 2012 6:05 am
Re: date being stored incorrectly in database
but how do i use the variable that is returned?Celauran wrote: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 wrote:$newDate = new DateTime($TRXDATE);
then used the $newDate to update the DB
$TRXDATE
Re: date being stored incorrectly in database
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
ok i get thatCelauran 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.
i have tried
i tried to use the $date to update the DB$newDate = $TRXDATE;
$date = new DateTime($newDate);
but i dont want to echo out at this point.
Re: date being stored incorrectly in database
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
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());Re: date being stored incorrectly in database
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
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());