Is there a way for me to specify the date function in MySQLWorkBench so that I won’t get an error me-CodePudding

I tried to create a table, this is how I set it up:

CREATE TABLE emp_tab
(  
  empno             NUMeric(10),  
  name              VARCHAR(50) NOT NULL,  
  job               VARCHAR(50),  
  manager           NUMeric(10),  
  hiredate          DATE,  
  salary            NUMeric(10,2),  
  commission        NUMeric(10,2),  
  deptno            NUMeric(5),  
  CONSTRAINT pk_emp_tab PRIMARY KEY (empno),  
  CONSTRAINT fk_emp_tab_deptno FOREIGN KEY (deptno) 
  REFERENCES dept_tab(deptno)  
);

this is how I insert values:

INSERT INTO emp_tab
 VALUES(7004, 'SCOTT', 'ANALYST', 7002,  
 date('87-7-13') - 85,  
 3000, null, 70
);


INSERT INTO emp_tab 
VALUES(7007, 'ADAMS', 'CLERK', 7003,  
 date('87-7-13') - 51,  
 1100, null, 40  
);

Oddly enough, I did not get an error message for the first inserted value but I got the error message for the second inserted value that says "Incorrect date value: '19870662' for column 'hiredate' at row 1) but after I removed - 51, it worked. However, it will give me incorrect date so I am wondering if there is any chance I can keep - 51 without getting errors?

CodePudding user response：

You should use a well formatted date and DATE_SUB()

DATE_SUB(DATE('1987-07-13'), INTERVAL 85 DAY)

CodePudding user response：

SQL

INSERT INTO emp_tab 
VALUES(7007, 'ADAMS', 'CLERK', 7003,
DATE_ADD(NOW(), INTERVAL -15 DAY),
 1100, null, 40  
);

MySql

The DATE_ADD() is use to add DAYS, MONTHS, YEARS

DATE_ADD(NOW(), INTERVAL -15 DAY)

Example

DATE_ADD("date", INTERVAL 15 DAY)
DATE_ADD("date", INTERVAL -2 MONTH)
DATE_ADD("date", INTERVAL 2 YEAR)