CODEWITHSUNDEEP
sqloracle-database
user1777711
user1777711

Reputation: 1744

Oracle SQL , how to use current date - a specified date

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

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

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?

Upvotes: 7

Views: 77125

Answers (5)

VYGA
VYGA

Reputation: 1

    SELECT extract(year from sysdate)-extract(year from TDATE) 
      FROM R_M 
CONNECT BY LEVEL <=1

Upvotes: -1

Tebbe
Tebbe

Reputation: 1372

I think you might be better served by a combination of FLOOR and 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.

Upvotes: 1

RHamblin
RHamblin

Reputation: 43

If you can measure it in months, try the months_between function:

http://www.techonthenet.com/oracle/functions/months_between.php

http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions094.htm#i78039

Could take that and divide by 12 to get the year.

Upvotes: 0

rs.
rs.

Reputation: 27427

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;

Upvotes: 10

Randy
Randy

Reputation: 16677

you do not need dual.sysdate - you can just reference sysdate.

select sysdate-staff.startdatefrom from staff

Upvotes: 1

Related Questions