How do you set a default value for a MySQL Datetime column?

Here is how to do it on MySQL 5.1:

ALTER TABLE `table_name` CHANGE `column_name` `column_name`
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

I have no clue why you have to enter the column name twice.

While you can't do this with `DATETIME` in the default definition, you can simply incorporate a select statement in your insert statement like this:

INSERT INTO Yourtable (Field1, YourDateField) VALUES('val1', (select now()))

Note the lack of quotes around the table.

For MySQL 5.5

If you are trying to set default value as NOW(),MySQL supports that you have to change the type of that column TIMESTAMP instead of DATETIME. TIMESTAMP have current date and time as default..i think it will resolved your problem..

In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition:

CREATE TABLE foo (
`creation_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`modification_time` DATETIME ON UPDATE CURRENT_TIMESTAMP
)

Reference:

[To see links please register here]

CREATE TABLE `testtable` (
`id` INT(10) NULL DEFAULT NULL,
`colname` DATETIME NULL DEFAULT '1999-12-12 12:12:12'
)


In the above query to create 'testtable', i used '1999-12-12 12:12:12' as default value for DATETIME column `colname`

[MySQL 5.6 has fixed this problem][1].

ALTER TABLE mytable CHANGE mydate datetime NOT NULL DEFAULT 'CURRENT_TIMESTAMP'


[1]:

[To see links please register here]

Take for instance If I had a table named 'site' with a created_at and an update_at column that were both DATETIME and need the default value of now, I could execute the following sql to achieve this.

<pre>
ALTER TABLE `site` CHANGE `created_at` `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `created_at` `created_at` DATETIME NULL DEFAULT NULL;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `site` CHANGE `updated_at` `updated_at` DATETIME NULL DEFAULT NULL;
</pre>

The sequence of statements is important because a table can not have two columns of type TIMESTAMP with default values of CUREENT TIMESTAMP

This is my trigger example:

<!-- begin snippet: js hide: false -->

<!-- language: lang-html -->

/************ ROLE ************/
drop table if exists `role`;
create table `role` (
`id_role` bigint(20) unsigned not null auto_increment,
`date_created` datetime,
`date_deleted` datetime,
`name` varchar(35) not null,
`description` text,
primary key (`id_role`)
) comment='';

drop trigger if exists `role_date_created`;
create trigger `role_date_created` before insert
on `role`
for each row
set new.`date_created` = now();

<!-- end snippet -->

For all those who lost heart trying to set a default **DATETIME** value in **MySQL**, I know exactly how you feel/felt. So here is is:


ALTER TABLE `table_name` CHANGE `column_name` DATETIME NOT NULL DEFAULT 0


Carefully observe that **I haven't added single quotes/double quotes** around the **0**

I'm literally jumping after solving this one :D

I'm running MySql Server 5.7.11 and this sentence:

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '0000-00-00 00:00:00'

is **not** working. But the following:

ALTER TABLE table_name CHANGE date_column datetime NOT NULL DEFAULT '1000-01-01 00:00:00'

**just works**.

As a *sidenote*, it is mentioned in the [mysql docs][1]:

> The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

even if they also say:

> Invalid DATE, DATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00').


[1]:

[To see links please register here]