Question 20552 in Mysql Question posted in
The official MySQL documentation can be found here.

How to set the default value for a DateTime column in MySQL workbench?

I have a column CreatedDate and I want to set the below default value in the below format for this column.

Default value:- yyyy-mm-dd 00:00:00.000

I have tried multiple solutions like I have used DATETIME datatype, TIMESTAMP but it is not taking the default value.

Could someone please share your thoughts on this?

Thanks,

Tags:

2

Answers


  1. 0

    The format you show is the default datetime format if you define the column with millisecond precision.

    Demo:

    mysql> create table mytable (id serial primary key, ts datetime(3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into mytable set ts = now();
Query OK, 1 row affected (0.01 sec)

mysql> select * from mytable;
+----+-------------------------+
| id | ts                      |
+----+-------------------------+
|  1 | 2023-03-06 09:15:34.000 |
+----+-------------------------+

    There is no way to change the default format of a datetime. Datetimes are not stored as a string, they are stored as a binary value. You must use the DATE_FORMAT() function to display a different format.

    Login or Signup to reply.
  2. 0

    If you just want to set the default value to a specific hard-coded value, you can use the following

    ALTER TABLE tableName ALTER CreatedDate SET DEFAULT "2017-06-28 00:00:00.000";

    See ALTER TABLE Statement docs

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search