我尝试运行并从表中的日期获取年龄,但最终出现错误"ORA-00923:在预期的地方找不到 FROM 关键字"。
这是我第一次在顶级预言机中运行
出货表
ship_id ship_date
SEL0001 12/6/2015
SEL0002 01/5/2016
SQL 代码
select ship_id, YEAR(ship_date) AS [BirthDate],
YEAR(GETDATE()) AS [ThisYear],
YEAR(GETDATE()) -YEAR(ship_date) AS Age
from shipping
预期产出
ship_id BirthDate ThisYear Age
SEL0001 12/6/2015 2020 5
SEL0002 01/5/2016 2020 4
有什么办法可以得到年龄吗?我尝试使用日期差异,但我不是这方面的专家。谢谢。
你应该在Oracle中使用extract
,这是演示。
select
ship_id,
TO_CHAR(ship_date , 'MM/DD/YYYY') as BirthDate,
extract(YEAR from sysdate) as ThisYear,
extract(YEAR from sysdate) - extract(YEAR from ship_date) as Age
from shipping
输出:
*---------------------------------------*
| SHIP_ID BIRTHDATE THISYEAR AGE |
*---------------------------------------*
| SEL0001 12/06/2015 2020 5 |
| SEL0002 01/05/2016 2020 4 |
*---------------------------------------*
使用extract
获取年或月值,对于年龄,使用"months_between"获取月数,然后计算年龄。
select ship_id,
to_char(ship_date,'DD/MM/YYYY') "BirthDate",
extract(year from SYSDATE) ThisYear,
months_between(sydate,ship_date)/12 as Age_inYears
from shipping