This problem describing the date format to inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The date can store only this type, but it can be used with many time format functions to change it and display it.
When write a query in MySQL using PHP it’s applicability will be checked on the basis of MySQL itself. So use default date and time format as provided by MySQL i.e. ‘YYYY-MM-DD’
DATE: YYYY-MM-DD Example: 2005-12-26 DATETIME: YYYY-MM-DD HH:MI:SS Example: 2005-12-26 23:50:30 TIMESTAMP: YYYY-MM-DD HH:MI:SS Example: 2005-12-26 23:50:30 YEAR: YYYY or YY
MySQL query to create DataBase:
CREATE DATABASE Date_time_example;
Example 1: PHP program to Create database and table
Table date_test created successfully
Example 2: PHP program to insert date into the table.
New record created successfully
Example 3: This example is used to display which row created on 2018-12-05. Use the following query to display result.
The created_at column contains not only date but also time. So it will display error message.
SELECT * FROM date_test WHERE created_at = '2018-12-05';
(!Important) Wrong Query It returns no rows
Correct Query: To correct it, use the DATE function as follows:
SELECT * FROM date_test WHERE DATE( created_at ) = '2018-12-05';
To get the year, quarter, month, week, day, hour, minute, and second from a DATETIME value, use the functions as shown in the following statement:
HOUR(@dt), MINUTE(@dt), SECOND(@dt), DAY(@dt), WEEK(@dt), MONTH(@dt), QUARTER(@dt), YEAR(@dt);
- PHP | Inserting into MySQL database
- PHP | Converting string to Date and DateTime
- PHP program to change date format
- PHP | MySQL WHERE Clause
- PHP | MySQL LIMIT Clause
- PHP | MySQL Delete Query
- PHP | MySQL ORDER BY Clause
- PHP | MySQL Select Query
- PHP | MySQL Database Introduction
- PHP | MySQL UPDATE Query
- What is the difference between MySQL, MySQLi and PDO?
- PHP | MySQL ( Creating Table )
- PHP | MySQL ( Creating Database )
- Nodejs – Connect Mysql with Node app
- MySQL | Change User Password
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.