Insert DATE and DATETIME Values in MySQL

MySQL stores calendar dates in DATE columns and date-plus-time values in DATETIME or TIMESTAMP columns. Insert values in a format MySQL can interpret for the target datatype.

INSERT INTO dt_tb (dt, dt2)
VALUES (
    '2026-09-09 10:52:00',
    '2026-09-09'
);

In this example, dt is a DATETIME column and dt2 is a DATE column.

DATE and DATETIME Formats Top ↑

A MySQL DATE value is normally written as:

'YYYY-MM-DD'

Example:

'2026-09-09'

A MySQL DATETIME value is normally written as:

'YYYY-MM-DD HH:MM:SS'

Example:

'2026-09-09 10:52:00'

Modern Sample Table Top ↑

The previous version of this tutorial used zero dates such as '0000-00-00' and repeated three UNIQUE indexes on the same id column. Those definitions are unnecessary and can conflict with modern strict SQL modes.

Use a normal primary key and real DATE/DATETIME columns:

CREATE TABLE dt_tb (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    dt DATETIME NOT NULL,
    dt2 DATE NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

See CREATE TABLE for column, key, and datatype design.

Insert Fixed Date Values Top ↑

INSERT INTO dt_tb (dt, dt2)
VALUES (
    '2004-05-05 23:56:25',
    '2005-06-12'
);

The values match the datatypes:

• dt receives a DATETIME value.
• dt2 receives a DATE value.

Insert the Current Date and Time Top ↑

When MySQL itself should supply the current values, use NOW() and CURDATE():

INSERT INTO dt_tb (dt, dt2)
VALUES (
    NOW(),
    CURDATE()
);

This is simpler than calculating the same current values in PHP and concatenating them into SQL.

Insert PHP Date Values with PDO Top ↑

Sometimes the application deliberately supplies the date, for example because it uses a specific application time zone or receives a validated date from another system.

Format the PHP values, then bind them with PDO rather than concatenating them into SQL:

<?php
require "config.php";

$now=new DateTimeImmutable(
    'now',
    new DateTimeZone('Asia/Kolkata')
);

$dt=$now->format('Y-m-d H:i:s');
$dt2=$now->format('Y-m-d');

$sql="INSERT INTO dt_tb (dt, dt2)
      VALUES (:dt, :dt2)";

try{
    $stmt=$dbo->prepare($sql);
    $stmt->bindValue(':dt', $dt, PDO::PARAM_STR);
    $stmt->bindValue(':dt2', $dt2, PDO::PARAM_STR);
    $stmt->execute();
    echo 'Date values stored.';
}catch(PDOException $e){
    error_log($e->getMessage());
    echo 'Unable to store the date values.';
}

See PDO connection and PDO INSERT.

PHP Time Zone vs MySQL Time Zone Top ↑

If PHP generates the date while MySQL generates other timestamps, confirm that both use the intended time zone.

PHP:

$now=new DateTimeImmutable(
    'now',
    new DateTimeZone('Asia/Kolkata')
);

MySQL session:

SELECT @@session.time_zone;

A mismatch can make "current" dates differ around midnight or can shift date-time values relative to application expectations.

Insert User-entered Dates Safely Top ↑

If a form supplies a date such as 09/09/2026, do not insert the raw text directly into a DATE column. Parse and validate it first.

PHP can convert an expected input format to the MySQL format:

<?php
$input='09/09/2026';

$date=DateTimeImmutable::createFromFormat(
    'd/m/Y',
    $input
);

if($date === false){
    echo 'Invalid date.';
}else{
    $mysql_date=$date->format('Y-m-d');
}

See PHP createFromFormat() for date parsing examples.

Convert Other Date Formats with STR_TO_DATE() Top ↑

MySQL's STR_TO_DATE() can parse text when the input format is known:

INSERT INTO dt_tb (dt, dt2)
VALUES (
    STR_TO_DATE(
        '09-09-2026 10:52:00',
        '%d-%m-%Y %H:%i:%s'
    ),
    STR_TO_DATE(
        '09-09-2026',
        '%d-%m-%Y'
    )
);

This is useful when the source text uses a known external format. For application form data, parsing and validating in the application layer can make validation rules easier to control.

Automatic Current Timestamp Top ↑

If a column should always record the insertion time, define that behavior in the table instead of supplying the value in every INSERT:

CREATE TABLE event_log (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    event_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

Then the INSERT can omit created_at:

INSERT INTO event_log (event_name)
VALUES ('User login');

Automatically Store the Last Update Time Top ↑

A separate column can track when a row was last modified:

CREATE TABLE article (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL
        DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

See automatic insertion and update timestamps.

NULL and Missing Dates Top ↑

If "no date has been assigned yet" is a valid state, model that with NULL rather than a fake zero date.

CREATE TABLE task (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    due_date DATE NULL,
    PRIMARY KEY (id)
);

Insert a missing date as NULL:

INSERT INTO task (due_date)
VALUES (NULL);

See SQL NULL values.

Invalid Dates and Strict SQL Mode Top ↑

Modern MySQL configurations commonly use strict SQL modes. Invalid dates can be rejected instead of silently becoming zero dates or unexpected values.

Examples that should not be used as normal application dates:

'0000-00-00'
'0000-00-00 00:00:00'
'2026-02-31'

Common Date-insert Mistakes Top ↑

Reversing the DATE and DATETIME columns Top ↑

Know which destination column expects only a date and which expects date + time.

Concatenating PHP values directly into SQL Top ↑

Use prepared statements for application-provided values.

Using zero dates as defaults Top ↑

They are poor placeholders and can be rejected by strict SQL modes.

Creating duplicate UNIQUE indexes on the same ID Top ↑

One PRIMARY KEY on an AUTO_INCREMENT ID is normally enough for row identity. Add other indexes only for real constraints or query needs.

Using PHP date() when MySQL should own the current timestamp Top ↑

For database-created timestamps, NOW(), CURDATE(), or a column default can be simpler and more consistent.

Ignoring time zones Top ↑

PHP and MySQL can produce different "current" values if they use different zones.

Storing display-formatted dates in VARCHAR columns Top ↑

Use proper DATE/DATETIME/TIMESTAMP datatypes, then format values for display.

Video Tutorial Top ↑

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: