Insert Date in MySQL

How to Insert a Date in MySQL

Using a database is mandatory for the creation of a dynamic modern website. MySQL has been established as a preferred database platform due to the indisputable qualities of this database server. Specifying the dates on which the content is entered is of prime importance for the structuring and the chronological arrangement of articles, posts and replies in a dynamic website. MySQL comes with several data types for storing dates in its database system: DATE, TIMESTAMP, DATETIME and YEAR.

How to use the DATE statement

The default way to store a date in a MySQL database is by using DATE. The proper format of a DATE is: YYYY-MM-DD. If you try to enter a date in a format other than the Year-Month-Day format, it might work but it won't be storing the dates as you expect.

In order to run a MySQL Insert command and add the current date into your table you can use MySQL's built-in function CURDATE() in your query.

An example of how to Insert a Date in MySQL using CURDATE

$query_auto = "INSERT INTO tablename (col_name, col_date) VALUE ('DATE: Auto CURDATE()', CURDATE() )";

Also, you can run a query to set the date manually

An example of how to Insert a Date in MySQL manually

$query_manual = "INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2008-7-04')";

All these queries can be run by using a command line, a script (written in PHP, Perl, Python etc.), or via the PHPMyAdmin interface inserted in NTC Hosting's sophisticated Web Hosting Control Panel.

Also, there is a statement that allows you to set only the YEAR of an event. The Year statement uses an YYYY format and has a range from 1901 to 2155 (for years outside this range you need to use the DATE statement)

Insert a date in MySQL using YEAR

Now let's check how the YEAR statement works in a query. For our example we will use CURDATE() again. As we mentioned above, CURDATE() provides a lot more information than just the year, but when we use the YEAR statement all the date information, besides the year, is ignored.

An example of how to Insert a YEAR in MySQL using CURDATE

INSERT INTO phonebook (col_name, col_date) VALUE ('YEAR: Auto CURDATE()', CURDATE() )";

Set a date in MySQL using DATETIME

Using DATETIME you can store both the date and the time. Its format is YYYY-MM-DD HH:mm:SS. Using this statement you can store the output for both DATE and TIME statements. Also, you can choose to store the date information only, but you can't store just the time.

An example of how to use DATETIME

INSERT INTO phonebook (col_name, col_date) VALUE ('DATETIME: Auto CURDATE()', CURDATE() )";

How to insert a date in PHPMyAdmin

As we mentioned above, there are several ways to store a date. One of the easiest methods is by using a database management tool such as PHPMyAdmin. With its graphical user interface and set of built-in features, PHPMyAdmin makes the database management an easy job. That’s why we, at NTC Hosting, have chosen the PHPMyAdmin web management tool and are providing it with all our web hosting plans.

To add a date information in PHPMyAdmin you need to log in the PHPMyAdmin interface and then to enter the 'Insert' tab. Then you can choose the appropriate Insert functions for each field using the drop-down list in the functions column.

If you want to learn more on how to use the insert function in PHPMyAdmin, please visit the MySQL Insert and PHPMyAdmin articles.

Setting a date in MySQL using a PHP script

Using a PHP script for setting a date in MySQL is very common. To use this method, run an SQL query in a PHP script. This method is used every day by some very simple PHP scripts and by large modern PHP-based applications. Let’s take a look at how it looks like:

An example of a PHP script which sets a date in MySQL manually

<?php
$query_date = "INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2008-7-04')”;
mysql_query($query_date) or die(mysql_error());
?>

Also, there is an option to set the date automatically:

An example of a PHP script which sets a date in MySQL manually

<?php
$query_date = "INSERT INTO tablename (col_name, col_date) VALUE ('DATE: Auto CURDATE()', CURDATE() )”;
mysql_query($query_date) or die(mysql_error());
?>

As you can assume, you can use not only DATE, but also YEAR and DATETIME statements in PHP - the syntax is similar.