Create table with date column

Question

I want to create a student table with column 'student_birthday' and its format should be dd-mm-yy.

create table `student`.`studentinfo`(
    `student_id` int(10) not null auto_increment,
    `student_name` varchar(45) not null,
    `student_surname` varchar(45) not null,
    `student_birthday` date(???),
    (some lines of code)
primary key(student_id));

what should be inputted in the (???) to get the right format above?

Answer 1

Just use "DATE" without the brackets. The brackets are only needed for certain column types where you want to specify the maximum number of bytes/characters that can be stored.

For MySQL, it's documented at https://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html

Answer 2

The following example will explain your problem. I am using MySQL 5.7.18.

Firstly I have described the structure of users table as I am going to create posts table with FOREIGN KEY.

Later I created posts table and it has a DATE field named created with many other columns.

Finally I inserted 1 row in the newly created table.

mysql> desc users;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | bigint(20)  | NO   | PRI | NULL    |       |
| fname       | varchar(50) | NO   |     | NULL    |       |
| lname       | varchar(50) | NO   |     | NULL    |       |
| uname       | varchar(20) | NO   |     | NULL    |       |
| email       | text        | NO   |     | NULL    |       |
| contact     | bigint(12)  | NO   |     | NULL    |       |
| profile_pic | text        | NO   |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql> CREATE TABLE  posts(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, title text NOT NULL, description text NOT NULL, posted_by bigint, FOREIGN KEY(posted_by) REFERENCES users(id) ON DELETE CASCADE ON UPDATE RESTRICT , created DATE);
Query OK, 0 rows affected (0.01 sec)

mysql> desc posts;                                                                                                                                                          
+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | bigint(20) | NO   | PRI | NULL    | auto_increment |
| title       | text       | NO   |     | NULL    |                |
| description | text       | NO   |     | NULL    |                |
| posted_by   | bigint(20) | YES  | MUL | NULL    |                |
| created     | date       | YES  |     | NULL    |                |
+-------------+------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> INSERT INTO posts(title, description, posted_by, created) values("Getting started with MySQL", "Excellent Database system", 1, "2017-05-26");
Query OK, 1 row affected (0.00 sec)

mysql> select * from posts;
+----+----------------------------+---------------------------+-----------+------------+
| id | title                      | description               | posted_by | created    |
+----+----------------------------+---------------------------+-----------+------------+
|  1 | Getting started with MySQL | Excellent Database system |         1 | 2017-05-26 |
+----+----------------------------+---------------------------+-----------+------------+
1 row in set (0.00 sec)

mysql>