CODEWITHSUNDEEP
mysqldatabase
whd
whd

Reputation: 1861

How to initialize date FIELD IN MYSQL

I Wanted to initialize default date field in my table with CURRENT_DATE(); well BENCH gives me errors that is not possible i heard about triggers but it seems to be a little over complicatet for that problem so is there any way to make this in such way

           CREATE TABLE Sprzedaz (
        Id int unsigned primary key auto_increment,
        KlientId int not null,
        ProduktNumer int not null,
        Ilosc int not null,
        Cena float not null,
        Data date default CURRENT_DATE(),
        check (Data >= now()),
         ....
          );

Upvotes: 1

Views: 1790

Answers (3)

vogomatix
vogomatix

Reputation: 5061

If you have MySQL version 5.6.5 and above, you can use CURRENT_TIMESTAMP instead of CURRENT_DATE

See http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

So you can't do it with dates but you may be able to change your field to be a TIMESTAMP or DATETIME and Bobs your mothers brother!

Upvotes: 0

fthiella
fthiella

Reputation: 49089

You can initialize a TIMESTAMP column with this:

Data TIMESTAMP DEFAULT CURRENT_TIMESTAMP

or a DATETIME column (MySQL 5.6+):

Data DATETIME DEFAULT CURRENT_TIMESTAMP

but if you want to initialize a DATE column using MySQL 5.5 you need an INSERT TRIGGER:

CREATE TRIGGER setdate_before_insert BEFORE INSERT ON test
FOR EACH ROW
  SET NEW.Data = CURDATE();

and maybe you need also an UPDATE trigger? Please see fiddle here.

Upvotes: 1

Ryan
Ryan

Reputation: 28227

Another way to go about this, if you do not mind changing the date type, would be to use a TIMESTAMP and initialize it with TIMESTAMP DEFAULT CURRENT_TIMESTAMP. Your definition would then become:

 CREATE TABLE Sprzedaz (
    Id int unsigned primary key auto_increment,
    KlientId int not null,
    ProduktNumer int not null,
    Ilosc int not null,
    Cena float not null,
    Data TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  <<<==== change this
    check (Data >= now()),
     ....
      );

Upvotes: 1

Related Questions