Setting default value for DATE type column to current date without time part?

It seems to work in sqlite:

"date" DATE NOT NULL DEFAULT (DATE(CURRENT_TIMESTAMP))

As noted in this question Invalid default value for 'create_date' timestamp field, this error may happen when MySQL is in strict mode (which is default behavior, I believe).

If you want to override it, just disable all these checks when creating your table:

SET SQL_MODE='ALLOW_INVALID_DATES';

The warning will be still generated, however it will allow to create the table.

Probably you cannot set default value for 'date' data type in mysql. You need to change the type to timestamp or datetime.

You may have a look at this similar question.

Invalid default value for 'Date'

EDIT:

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: http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

MySQL 8.0+:

CREATE TABLE foo (
    `creation_time`     DATE DEFAULT (DATE_FORMAT(NOW(), '%Y-%m-%d'))
)