PHP mysql insert date format

Question

I'm using jQuery datepicker the format of the datepicker is this 08/25/2012

I have errors when inserting to my database it inserts only 0000-00-00 00 00 00

My code is:

<?php
$id = $_POST['id'];
$name = $_POST['name'];
$date = $_POST['date'];
$sql = mysql_query( "INSERT INTO user_date VALUE( '', '$name', '$date')" ) or die ( mysql_error() );
echo 'insert successful';
?>

I'm sure my insert is correct.

Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Aug 25 '12 at 08:47

score 183 · Accepted Answer · edited May 23 '17 at 12:02

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 or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 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 its altFormat 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 the dateFormat 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 or TIMESTAMP column for holding a date value; I recommend you consider using MySQL's DATE type instead. As explained in The DATE, DATETIME, and TIMESTAMP 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 displays DATETIME 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.

score 10 · Answer 2 · answered Aug 25 '12 at 08:33

10

You should consider creating a timestamp from that date witk mktime()

eg:

$date = explode('/', $_POST['date']);
$time = mktime(0,0,0,$date[0],$date[1],$date[2]);
$mysqldate = date( 'Y-m-d H:i:s', $time );

answered Aug 25 '12 at 08:33

Mihai Iorga

39,330
16
106
107

Kishan Chauhan · Answer 3 · 2015-04-06T10:56:37.533

9

$date_field         = date('Y-m-d',strtotime($_POST['date_field']));
$sql = mysql_query("INSERT INTO user_date (column_name,column_name,column_name) VALUES('',$name,$date_field)") or die (mysql_error());

edited Apr 06 '15 at 10:56

answered Apr 06 '15 at 10:50

Kishan Chauhan

1,216
1
12
19

2

thanks, you give me hint `date('Y-m-d',strtotime($_POST['date_field']));` is works and this is exactly what I want. – Christhofer Natalius Oct 05 '15 at 08:45
Is there a way to push this higher up? It's very simple and works. The accepted answer is thorough, but doesn't appear to have this option. – ssaltman Jun 15 '16 at 00:33

score 1 · Answer 4 · edited May 23 '17 at 11:54

You must make sure that the date format is YYYY-MM-DD on your jQuery output. I can see jQuery returns MM-DD-YYYY, which is not the valid MySQL date format and this is why it returns an error.

To convert it to the right one you could do this:

$dateFormated = split('/', $date);
$date = $dateFormated[2].'-'.$dateFormated[0].'-'.$dateFormated[1];

Then you will get formatted date that will be valid MySQL format, which is YYYY-MM-DD, i.e. 2012-08-25

I would also recommend using mysql_real_escape_string as you insert data into database to prevent SQL injections as a quick solution or better use PDO or MySQLi.

Your insert query using mysql_real_escape_string should rather look like this:

$sql = mysql_query( "INSERT INTO user_date VALUE( '', '" .mysql_real_escape_string($name). "', '" .mysql_real_escape_string($date). "'" ) or die ( mysql_error() );

score 1 · Answer 5 · answered Aug 17 '15 at 08:14

Get a date object from the jquery date picker using

var myDate = $('element').datepicker('getDate')

For mysql the date needs to be in the proper format. One option which handles any timezone issues is to use moment.js

moment(myDate).format('YYYY-MM-DD HH:mm:ss')

score 0 · Answer 6 · answered Dec 15 '14 at 02:42

0

The simplest method is

$dateArray = explode('/', $_POST['date']);
$date = $dateArray[2].'-'.$dateArray[0].'-'.$dateArray[1];

$sql = mysql_query("INSERT INTO user_date (column,column,column) VALUES('',$name,$date)") or die (mysql_error());

answered Dec 15 '14 at 02:42

William Worley

815
1
8
13

score 0 · Answer 7 · edited Feb 06 '20 at 17:08

0

HTML:

<div class="form-group">
  <label for="pt_date" class="col-2 col-form-label">Date</label>
  <input class="form-control" type="date" value=<?php echo  date("Y-m-d") ;?> id="pt_date" name="pt_date">
</div>

SQL

$pt_date = $_POST['pt_date'];

$sql = "INSERT INTO `table` ( `pt_date`) VALUES ( '$pt_date')";

edited Feb 06 '20 at 17:08

Sergiy T.

1,433
1
23
25

answered Feb 06 '20 at 13:13

Pupan Poulkaew

1

Date id="pt_date" name="pt_date"> – Pupan Poulkaew Feb 06 '20 at 13:14
Please add some explanation to your answer such that others can learn from it. Additionally, put all information in your answer and don't use the comment section to contain more information – Nico Haase Feb 06 '20 at 13:32
No researchers should use this query -- it is insecure/unstable. – mickmackusa Jun 18 '21 at 11:55

Santiago Hernan Bareiro · Answer 8 · 2021-06-18T11:18:16.760

Suppose you receive a Christmas date: December 25, 2021 (25 de Diciembre del año 2021) in string format: "d-m-Y" for example "12-25-2021". First create a valid DateTime object from the received format:

var_dump(DateTime::createFromFormat("d-m-Y","25-12-2021", new DateTimeZone("America/Argentina/Buenos_Aires")));

This generates as output:

object(DateTime)#2 (3) {
  ["date"]=>
  string(26) "2021-12-25 10:21:11.000000"
  ["timezone_type"]=>
  int(3)
  ["timezone"]=>
  string(30) "America/Argentina/Buenos_Aires"
}

Now to get the date field from the DateTime object ready to go using format("Y-m-d H:i:s")):

var_dump(DateTime::createFromFormat("d-m-Y","25-12-2021",new DateTimeZone("America/Argentina/Buenos_Aires"))->format("Y-m-d H:i:s"));

This generates as output a ready to insert value:

string(19) "2021-12-25 10:56:30"

In summary now you can insert a formated date ('DD-MM-YYYY') in MySQL like this:

<?php
//string input post like "25-12-2021"
$date_input = $_POST['date_input']; 
//create DateTime object
$date_time_obj=DateTime::createFromFormat("d-m-Y","25-12-2021",new DateTimeZone("America/Argentina/Buenos_Aires"));
//format date ready to insert as string at MySQL
$str_date=$date_time_obj->format("Y-m-d H:i:s");
?>

Now run directly from PHP to MySQL as a query:

<?php
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');

//verify connection
if (mysqli_connect_errno()) {
    printf("Error de conexión: %s\n", mysqli_connect_error());
    exit();
}
//prepare the query to execute
$stmt = $mysqli->prepare("INSERT INTO table_example (DATE_FIELD) VALUES (?)");
//prevent sql injection
$stmt->bind_param('s', $str_date);
//execute prepared statements
$stmt->execute ();
?>

Why do you do `DateTime::createFromFormat("dmY", str_replace("-","","25-12-2021"), new DateTimeZone("America/Argentina/Buenos_Aires"))` instead of `DateTime::createFromFormat("d-m-Y", "25-12-2021", new DateTimeZone("America/Argentina/Buenos_Aires"))` — shaedrich, Jun 18 '21 at 08:21
`"INSERT INTO table_example (DATE_FIELD) VALUES ('$str_date')"` is open to SQL injections. Use parameter binding instead. — shaedrich, Jun 18 '21 at 08:22

score 0 · Answer 9 · edited Jan 13 '22 at 18:05

0

These days, I am not good with dates. Thanks to the following work, I can print the dates in the format I want.

$year = $_POST['year'];
$month = $_POST['month'];
$day = $_POST['day'];
$scr = strtotime($year . '-' . $month . '-' . $day);
$gelis_tarihi = date('Y-m-d', $scr);

$yil = $_POST['yil'];
$ay = $_POST['ay'];
$gun = $_POST['gun'];
$birlestir = strtotime($yil . '-' . $ay . '-' . $gun);
$gelis_tarihi = date('Y-m-d', $birlestir);

edited Jan 13 '22 at 18:05

msrumon

1,250
1
10
27

answered Jan 13 '22 at 14:54

EngineerHT

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 14 '22 at 03:00

score -2 · Answer 10 · edited Jul 28 '14 at 10:19

-2

Try Something like this..

echo "The time is " . date("2:50:20");
$d=strtotime("3.00pm july 28 2014");
echo "Created date is " . date("d-m-y h:i:sa",$d);

edited Jul 28 '14 at 10:19

MarmiK

5,639
6
40
49

answered Jul 28 '14 at 09:48

shubham prajapati

1

score -2 · Answer 11 · edited May 04 '16 at 05:34

-2

First of all store $date=$_POST['your date field name'];

insert into **Your_Table Name** values('$date',**other fields**);

You must contain date in single cote (' ')

I hope it is helps.

edited May 04 '16 at 05:34

ketan

19,129
42
60
98

answered May 04 '16 at 05:20

Anand Raj Mehta

437
5
4

PHP mysql insert date format

11 Answers11

Warning

Linked

Related