How to specify date function in MySQLWorkBench to avoid error prompts?

Question

I tried creating a table and this is how I set it up:

CREATE TABLE emp_tab
(
  empnoNUMeric(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 the 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
);

The weird thing is that I don't get an error message for the first inserted value, but I get an error message for the second inserted value that reads "Incorrect date value: Column "Hire Date" for row 1 is "19870662"), but then I removed the -51 and it worked. However, it gives me the wrong date, so I'm wondering if there's any chance of keeping the -51 without getting the error?

Answer 1

You should use a properly formatted date with DATE_SUB()

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