Oracle SQL ，如何使用当前日期 - 指定日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13253009/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL , how to use current date - a specified date
提问by user1777711
For getting current date i use this..
为了获取当前日期，我使用这个..
select extract(year from sysdate) from dual;
For getting the date that my database hold i use this..
为了获取我的数据库保存的日期，我使用这个..
select extract(year from startdate) from staff;
But now i am trying to update a field call serviceYears inside staff, by using
但是现在我正在尝试通过使用更新员工内部的现场呼叫 serviceYears
current year - year of start date
current year - year of start date
to get the amount of years the staff have committed to work. how do i achieve it..
获得员工承诺工作的年数。我如何实现它..
i am using oracle sql
我正在使用 oracle sql
Thanks!
谢谢！
I tried to use
我试着用
SQL> select dual.sysdate-staff.startdatefrom dual,staff;
select dual.sysdate-staff.startdatefrom from dual,staff
*
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
I also tried
我也试过
select SYSDATE-to_date('01-jan-2007','dd-mon-yyyy') from dual;
But it return me
SYSDATE-TO_DATE('01-JAN-2007','DD-MON-YYYY')
--------------------------------------------
2136.93719
How do i just get the year?
我如何获得年份？
回答by rs.
You can do this
你可以这样做
UPDATE STAFF
SET serviceYear = ROUND((sysdate - startDate)/365)
Ex:
前任：
select ROUND((sysdate - to_date('01-JAN-2007','DD-MON-YYYY'))/365)
from dual; -- returns 6
select ROUND((sysdate - to_date('01-JAN-2005','DD-MON-YYYY'))/365,2)
from dual; -- returns 7.85
Updated:
更新：
SELECT
FLOOR((SYSDATE - TO_DATE('01-JAN-2005','DD-MON-YYYY'))/365) YEARDOWN,
CEIL((SYSDATE - TO_DATE('01-JAN-2005','DD-MON-YYYY'))/365) YearUP
FROM DUAL;
回答by Randy
you do not need dual.sysdate - you can just reference sysdate.
你不需要dual.sysdate——你可以只引用sysdate。
select sysdate-staff.startdatefrom from staff
回答by Tebbe
I think you might be better served by a combination of FLOORand MONTHS_BETWEEN.
我认为FLOOR和的组合可能会更好地为您服务MONTHS_BETWEEN。
SQL> CREATE TABLE t (start_date DATE);
Table created.
SQL> INSERT INTO t VALUES (TO_DATE('20070930','YYYYMMDD'));
1 row created.
SQL> SELECT FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), start_date)/12) yrs_between_start_dt_and_today FROM t
2 ;
YRS_BETWEEN_START_DT_AND_TODAY
------------------------------
5
SQL>
You can adjust your rounding as needed. Moreover, this solution plays better with leap years and such, as compared to dividing by a hard-coded 365.
您可以根据需要调整舍入。此外，与除以硬编码 365 相比，此解决方案在闰年等方面表现更好。
回答by RHamblin
If you can measure it in months, try the months_between function:
如果您可以以月为单位进行衡量，请尝试使用months_between 函数：
http://www.techonthenet.com/oracle/functions/months_between.php
http://www.techonthenet.com/oracle/functions/months_between.php
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions094.htm#i78039
http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions094.htm#i78039
Could take that and divide by 12 to get the year.
可以把它除以 12 得到年份。
回答by VYGA
SELECT extract(year from sysdate)-extract(year from TDATE)
FROM R_M
CONNECT BY LEVEL <=1
