Use Current Date as Default Value for a Column

use current date as default value for a column

Add a default constraint with the GETDATE() function as value.

ALTER TABLE myTable 
  ADD CONSTRAINT CONSTRAINT_NAME
    DEFAULT GETDATE() FOR myColumn

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

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

How do I set the default value of a column to today's date?

In MySL you could use the function CURDATE() to retun only the date as YYYY-MM-DD.

Reference: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

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

IMPORTANT EDIT:
It is now possible to achieve this with DATETIME fields since MySQL 5.6.5, take a look at the other post below...

Previous versions can't do that with DATETIME...

But you can do it with TIMESTAMP:

mysql> create table test (str varchar(32), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.00 sec)

mysql> desc test;
+-------+-------------+------+-----+-------------------+-------+
| Field | Type        | Null | Key | Default           | Extra |
+-------+-------------+------+-----+-------------------+-------+
| str   | varchar(32) | YES  |     | NULL              |       | 
| ts    | timestamp   | NO   |     | CURRENT_TIMESTAMP |       | 
+-------+-------------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

mysql> insert into test (str) values ("demo");
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+---------------------+
| str  | ts                  |
+------+---------------------+
| demo | 2008-10-03 22:59:52 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql>

CAVEAT: IF you define a column with CURRENT_TIMESTAMP ON as default, you will need to ALWAYS specify a value for this column or the value will automatically reset itself to "now()" on update. This means that if you do not want the value to change, your UPDATE statement must contain "[your column name] = [your column name]" (or some other value) or the value will become "now()". Weird, but true. I am using 5.5.56-MariaDB

Create a column 'Date' with default value current datetime MYSQL

From the document

The DEFAULT value clause in a data type specification indicates a
default value for a column. With one exception, the default value must
be a constant; it cannot be a function or an expression. This means,
for example, that you cannot set the default for a date column to be
the value of a function such as NOW() or CURRENT_DATE. The exception
is that you can specify CURRENT_TIMESTAMP as the default for TIMESTAMP
and DATETIME columns

So no function is allowed in the default value hence the first query is failing.

Again from the document

As of MySQL 5.6.5, TIMESTAMP and DATETIME columns can be automatically
initializated and updated to the current date and time (that is, the
current timestamp). Before 5.6.5, this is true only for TIMESTAMP, and
for at most one TIMESTAMP column per table. The following notes first
describe automatic initialization and updating for MySQL 5.6.5 and up,
then the differences for versions preceding 5.6.5.

Before 5.6.5, this is true only for TIMESTAMP

So your mysql version is less than 5.6.5 hence the 2nd query is failing too.

So you need to create the table as

CREATE TABLE IF NOT EXISTS 
USER_PROFILE
(
  Id INT PRIMARY KEY AUTO_INCREMENT, 
  date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ;

SQL Default value current date and time

Just:

enteredOn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP

Note: for this to work, your column must be declared as a DATETIME or a TIMESTAMP (not as a DATE).

If you do want a DATE, then you would need a trigger:

CREATE TRIGGER Trg_Hospital_MedicalRecord_EnteredOn 
BEFORE INSERT ON Hospital_MedicalRecord
FOR EACH ROW 
SET NEW.enteredOn= CURRENT_DATE();

how to create new table in mysql with date default value for current date?

You can use current_date

create table People (
    first_name varchar(15) not null,
    last_name varchar(15) not null,
    registration_date date not null DEFAULT (CURRENT_DATE)
); 

How to set default current date in jpa for mysql

I would recommend trying the CreationTimestamp annotation paired with nullable = false, updatable = false

@Column(name = "ddate", nullable = false, updatable = false)
@CreationTimestamp
private Date ddate;

The above strategy uses the current VM date, so use this if you are ok with that.

---

Related Topics