Mysql Datetime field, update only date using php

Question

I have an issue in updating only date in datetime field type in MYSQL.

I am taking the input from the user (dd/mm/yyyy) using PHP and changing only the date using UPDATE mysql statement. But the datetime field show only 0000-00-00 00:00:00.

Below code is only the php file which updates the table after taking the input from php which passes value using XMLHttprequest.

Pl see my code below:

<?php include 'accesscontrol.php'; ?>
<?php
$q4=$_GET["q4"];

$user = $_SESSION['user'];

$date_array = explode( "/", $q4 );
$new = sprintf( '%4d-%02d-%02d', $date_array[2], $date_array[1], $date_array[0] );


$con = mysql_connect("localhost","tst","tst!@#");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("test", $con);

mysql_query("UPDATE INVHDR_edit SET Invdate= concat ('$new', time(Invdate)) WHERE usr='$user'");


if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }

mysql_close($con);
?>

Sohail · Accepted Answer · 2012-12-05 10:40:13Z

2

try this

<?php include 'accesscontrol.php'; 
$q4 = $_GET["q4"];
$user = $_SESSION['user'];
$dateSql = date("Y-m-d H:i:s",strtotime($q4));
$con = mysql_connect("localhost","tst","tst!@#");
if (!$con){
  die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $con);
mysql_query("UPDATE INVHDR_edit SET Invdate= '$dateSql' WHERE usr='$user'");
if (!mysql_query($sql,$con)){
  die('Error: ' . mysql_error());
}
mysql_close($con);
?>

answered Dec 5, 2012 at 10:40

Sohail

337 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1114409 Over a year ago

Even this does't work, it stores this value 1970-01-01 05:30:00 in the field. I think because my $q4 value would be in 04/05/2012 format.

Sohail Over a year ago

mysql only allows you to insert in Y-m-d H:i:s format in datetime field.

user1114409 Over a year ago

True, if you see my php code above i am formatting the date and then storing it in Y-m-d format

mubarakrocky · Accepted Answer · 2012-12-05 10:43:48Z

1

Try with this query: UPDATE INVHDR_edit SET Invdate='".$new." 00:00:00' WHERE usr='$user'");

if you need time to append use following.

$new = $new.date(" H:i:s");
mysql_query("UPDATE INVHDR_edit SET Invdate='$new' WHERE usr='$user'");

edited Dec 5, 2012 at 10:43

answered Dec 5, 2012 at 10:30

mubarakrocky

487 bronze badges

1 Comment

user1114409 Over a year ago

In your response you have given time as 00:00:00, in that case, it will update 00 instead of keeping the existing time. Pl check

Sashi Kant · Accepted Answer · 2012-12-05 10:30:30Z

0

Try this ::

UPDATE INVHDR_edit SET Invdate= STR_TO_DATE(Invdate, '%d-%b-%y') WHERE usr='$user'");

If using a variable:

UPDATE INVHDR_edit SET Invdate= STR_TO_DATE(@var1, '%d-%b-%y') WHERE usr='$user'");

edited Dec 5, 2012 at 10:30

answered Dec 5, 2012 at 10:24

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

5 Comments

user1114409 Over a year ago

I want to take the date value from the variable, in your response, that is missing, kindly check

user1114409 Over a year ago

Tried this, it gives value NULL in the field (datetime)

Sashi Kant Over a year ago

Correct me if I am wrong, you need to update the column with a variable having a data as date? You dont want the column to be updated with the current time?

user1114409 Over a year ago

Yes you are right only date to be updated and hold the time which is already present in the datetime field.

Sashi Kant Over a year ago

Please store the date into a variable, and try to print that variable before the query execution, then by seeing the format, I will updated my query

Collectives™ on Stack Overflow

Mysql Datetime field, update only date using php

3 Answers 3

3 Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related