CODEWITHSUNDEEP
mysqldatabasemysql-error-1064
K''
K''

Reputation: 5228

set column to certain date in create table statement in mysql

I want to create a table that has a birthday date, this column shouldn't be null, and I want to add a default value to be the date of 1970-01-01, I tried this but it gives an error in mysql console:

create table __update_my_status.__user
(
__user_id          int unsigned   auto_increment,
__email            varchar(100)   collate utf8_bin    not null,
__password         varchar(30)    collate utf8_bin    not null,
__first_name       varchar(50)    collate utf8_bin    not null,
__last_name        varchar(50)    collate utf8_bin    not null,
__date_of_birth    date           default DATE ('1970-01-01'),
__profile_image    blob,
constraint __pk_user_id           primary key (__user_id)
);

the error is: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('1970-01-01'), __profile_image blob, constraint __pk_user_id prima' at line 8

I'm using MySql 5.1.58 on Ubuntu 11.10

Upvotes: 0

Views: 2627

Answers (2)

Marcus
Marcus

Reputation: 12586

You don't need to use the DATE operator in this case. You just add the value as a string and the database handles it automagically.

__date_of_birth date default '1970-01-01',

Upvotes: 1

Gus
Gus

Reputation: 7349

You can use the same format as if you were inserting a date into the column once the table has been created. Just use the string form, you don't need the DATE() function wrapper:

__date_of_birth    date           default '1970-01-01',

Upvotes: 0

Related Questions