PHP date() format when inserting into datetime in MySQL

Last Updated : 09 Dec, 2021
Comments
Improve
Suggest changes
Like Article
Like
Report
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' Examples: 
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 php 
<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "geeks";

// Create connection
$conn = mysqli_connect( $servername, $username, $password, $dbname );

// Check connection
if ( !$conn ) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL query to create table
$sql = "CREATE TABLE date_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at DATETIME
)";

if (mysqli_query($conn, $sql)) {
    echo "Table date_test created successfully";
} else {
    echo "Error creating table: " . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>
Output: 
Table date_test created successfully
Example 2: PHP program to insert date into the table. php 
<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "geeks";

// Create connection
$conn = mysqli_connect( $servername, $username, $password, $dbname );

// Check connection
if ( !$conn ) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL query to insert data into table
$sql = "INSERT INTO date_test( created_at ) 
        VALUES( '2018-12-05 12:39:16' );";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>
Output: 
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';
Output: 
(!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';
php 
<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "geeks";

// Create connection
$conn = mysqli_connect( $servername, $username, $password, $dbname );

// Check connection
if ( !$conn ) {
    die("Connection failed: " . mysqli_connect_error());
}

// SQL query 
$sql = "SELECT * FROM date_test
WHERE DATE(created_at) = '2018-12-05'";

$result = mysqli_query( $conn, $sql ); 

if ($result) {
    echo $result; //printing Query result
} 
else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

// Close connection
mysqli_close($conn);
?>
Output: 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 is a server-side scripting language designed specifically for web development. You can learn PHP from the ground up by following this PHP Tutorial and PHP Examples.


Next Article
PHP date() format when inserting into datetime in MySQL

S
SachinDiwakar1
Improve
Article Tags :

Similar Reads