请问各位大佬数据库怎么计算工龄,格式比如:2年5月10日

Question

如题所述

Answer 1

select years || '年' ||
months || '月' ||
abs(
trunc(
newer_date-
add_months(older_date,years*12+months )
)
) || '天'
from (
select trunc(months_between(newer_date, older_date )/12) YEARS,
mod(trunc(months_between(newer_date, older_date )),12 ) MONTHS,
newer_date,
older_date
from(
select to_date('2018-07-11','yyyy-mm-dd') older_date,
sysdate newer_date
from dual
)
);追问



追答

是oracle ,你是mysql嘛。mysql你可以参考以下。
select
concat(years,'年',
months,'月',
abs(
truncate(
datediff(newer_date,
date_add(older_date, interval years*12+months month ))
,0)
),'日')
from (
select truncate(TIMESTAMPDIFF(MONTH,older_date, newer_date )/12,0) YEARS,
mod(truncate(TIMESTAMPDIFF(MONTH,older_date, newer_date ),0),12 ) MONTHS,
newer_date,
older_date
from(
select str_to_date('2018-07-11','%Y-%m-%d') older_date,
str_to_date('2019-12-04','%Y-%m-%d') newer_date
from dual
) t
) y;

本回答被网友采纳