This problem describes the date format for inserting the date into MySQL database. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD HH:MM:SS’ format. The date can be stored in this format only. However, it can be used with any time format functions to change it and display it.
When writing 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
- How to get ID of the last updated row in MySQL?
- PHP | MySQL UPDATE Query
- PHP | MySQL LIMIT Clause
- What is the difference between MySQL, MySQLi and PDO?
- PHP | MySQL Database Introduction
- PHP | MySQL ( Creating Database )
- PHP | MySQL ( Creating Table )
- PHP | MySQL Delete Query
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. 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.
Improved By : AnnapurnaAgrawal