PHP date() format when inserting into datetime in MySQL
The problem is that you're using 'M'
and 'D'
, which are a textual representations, MySQL is expecting a numeric representation of the format 2010-02-06 19:30:13
Try: date('Y-m-d H:i:s')
which uses the numeric equivalents.
edit: switched G
to H
, though it may not have impact, you probably want to use 24-hour format with leading 0s.
Insert current date in datetime format mySQL
If you're looking to store the current time just use MYSQL's functions.
mysql_query("INSERT INTO `table` (`dateposted`) VALUES (now())");
If you need to use PHP to do it, the format it Y-m-d H:i:s
so try
$date = date('Y-m-d H:i:s');
mysql_query("INSERT INTO `table` (`dateposted`) VALUES ('$date')");
php format date into mysql format for insert
You can try this
$str = "03.03.2021";
$date = DateTime::createFromFormat("d.m.Y", $str);
echo date_format($date,"Y-m-d");
PHP: convert date to datetime and insert it into mysql database?
Do one thing,
$mysqlDate = date('Y-m-d H:i:s', strtotime(str_replace("/","-",$_POST['php_date'])));
echo $mysqlDate;
Give it a try,
it should work.
Note:
Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d.
Source link.
Your new concern answer,
$mysqlDate = date('Y-m-d H:i:s', strtotime(str_replace("/","-",$_POST['php_date']).' + '.rand(30,60*60*24*3).' seconds'));
PHP mysql insert date format
As stated in Date and Time Literals:
MySQL recognizes
DATE
values in these formats:
As a string in either
'YYYY-MM-DD'
or'YY-MM-DD'
format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example,'2012-12-31'
,'2012/12/31'
,'2012^12^31'
, and'2012@12@31'
are equivalent.As a string with no delimiters in either
'YYYYMMDD'
or'YYMMDD'
format, provided that the string makes sense as a date. For example,'20070523'
and'070523'
are interpreted as'2007-05-23'
, but'071332'
is illegal (it has nonsensical month and day parts) and becomes'0000-00-00'
.As a number in either
YYYYMMDD
orYYMMDD
format, provided that the number makes sense as a date. For example,19830905
and830905
are interpreted as'1983-09-05'
.
Therefore, the string '08/25/2012'
is not a valid MySQL date literal. You have four options (in some vague order of preference, without any further information of your requirements):
Configure Datepicker to provide dates in a supported format using an
altField
together with itsaltFormat
option:<input type="hidden" id="actualDate" name="actualDate"/>
$( "selector" ).datepicker({
altField : "#actualDate"
altFormat: "yyyy-mm-dd"
});Or, if you're happy for users to see the date in
YYYY-MM-DD
format, simply set thedateFormat
option instead:$( "selector" ).datepicker({
dateFormat: "yyyy-mm-dd"
});Use MySQL's
STR_TO_DATE()
function to convert the string:INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))
Convert the string received from jQuery into something that PHP understands as a date, such as a
DateTime
object:$dt = \DateTime::createFromFormat('m/d/Y', $_POST['date']);
and then either:
obtain a suitable formatted string:
$date = $dt->format('Y-m-d');
obtain the UNIX timestamp:
$timestamp = $dt->getTimestamp();
which is then passed directly to MySQL's
FROM_UNIXTIME()
function:INSERT INTO user_date VALUES ('', '$name', FROM_UNIXTIME($timestamp))
Manually manipulate the string into a valid literal:
$parts = explode('/', $_POST['date']);
$date = "$parts[2]-$parts[0]-$parts[1]";
Warning
Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don't know what I'm talking about, or how to fix it, read the story of Bobby Tables.
Also, as stated in the introduction to the PHP manual chapter on the
mysql_*
functions:This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.
You appear to be using either a
DATETIME
orTIMESTAMP
column for holding a date value; I recommend you consider using MySQL'sDATE
type instead. As explained in TheDATE
,DATETIME
, andTIMESTAMP
Types:The
DATE
type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in'YYYY-MM-DD'
format. The supported range is'1000-01-01'
to'9999-12-31'
.The
DATETIME
type is used for values that contain both date and time parts. MySQL retrieves and displaysDATETIME
values in'YYYY-MM-DD HH:MM:SS'
format. The supported range is'1000-01-01 00:00:00'
to'9999-12-31 23:59:59'
.The
TIMESTAMP
data type is used for values that contain both date and time parts.TIMESTAMP
has a range of'1970-01-01 00:00:01'
UTC to'2038-01-19 03:14:07'
UTC.
Insert date and time into Mysql
$datetime = $_POST['date'] . ' ' . $_POST['time'] . ':00';
$datetime = mysql_real_escape_string($datetime);
$query = "INSERT INTO table(timestamp) VALUES ('$datetime')";
alternative solution that can handle more formats:
$datetime = $_POST['date'] . ' ' . $_POST['time'];
$datetime = mysql_real_escape_string($datetime);
$datetime = strtotime($datetime);
$datetime = date('Y-m-d H:i:s',$datetime);
$query = "INSERT INTO table(timestamp) VALUES ('$datetime')";
Related Topics
How to Insert Element into Arrays At Specific Position
How to Check If an Email Address Is Real or Valid Using PHP
Connecting to Ws-Security Protected Web Service With PHP
Copy Image from Remote Server Over Http
How to Get the HTML Code of a Web Page in PHP
What Is the Canonical Way to Determine Commandline Vs. Http Execution of a PHP Script
Are There Good Tutorials on How to Use Pdo
The Character Encoding of the HTML Document Was Not Declared
How to Upload & Save Files With Desired Name
Accessing Outside Variable Using Anonymous Function as Params
When to Use Static VS Instantiated Classes
Can HTML Be Embedded Inside PHP "If" Statement