mysql - 在SQL中计算日期

Question

我在计算教职员工的受聘年限时遇到问题。 (当前时间 - 教师聘用日期)

我正在尝试使用它，但是我不断收到 DATEDIFF 是无效标识符的错误。请帮忙。

SELECT FAC_FN , DATEDIFF ('CURDATE()' , 'FAC_HIRE DATE')
FROM FACULTY;

--CREATING TABLE FACULTY

CREATE TABLE FACULTY
(
FAC_ID NUMBER (4) CONSTRAINT FAC_ID_PK PRIMARY KEY,
FAC_FN VARCHAR2 (15),
FAC_LN VARCHAR2 (15),
FAC_DEPT VARCHAR2 (10),
FAC_RANK VARCHAR2 (10),
FAC_HIRE_DATE DATE,
FAC_SALARY NUMBER (7),
FAC_SUPERVISOR NUMBER (4)
);

--INSERTING RECORDS INTO FACULTY TABLE

INSERT INTO FACULTY VALUES ( 9001 , 'Leonard' , 'Vince' , 'IS' , 'ASST' , TO_DATE('12-APR-1997','DD-MON-YYYY') , 67000 , 9003);

INSERT INTO FACULTY VALUES ( 9002 , 'Victor' , 'Strong' , 'CSCI' , 'ASSO' , TO_DATE('8-AUG-1999','DD-MM-YYYY') , 70000 , 9003);

INSERT INTO FACULTY VALUES ( 9003 , 'Nicki' , 'Colan' , 'IS' , 'PROF' , TO_DATE('20-AUG-1981','DD-MM-YYYY') , 75000, 9010);

INSERT INTO FACULTY VALUES ( 9004 , 'Fred' , 'Wells' , 'ACCT' , 'ASST' , TO_DATE('28-AUG-1996','DD-MM-YYYY'), 60000, 9010);

INSERT INTO FACULTY VALUES ( 9010 , 'Chris' , 'Macon' , 'ACCT' , 'ASST' , TO_DATE('4-AUG-1980','DD-MM-YYYY') , 75000 , '');

Answer 1

删除引号(它们会导致 MySQL 将函数参数解析为字符串文字):

SELECT FAC_FN, DATEDIFF(CURDATE(), FAC_HIRE_DATE) FROM FACULTY

另请注意 DATEDIFF()在天数内返回结果；要获得年份差异，您不妨使用 TIMESTAMPDIFF()相反。