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?
CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE. The following statement shows the current date in 'DD-MON-YYYY HH24:MI:SS' format : SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; Session altered.
To get the current date and time in SQL Server, use the GETDATE() function. This function returns a datetime data type; in other words, it contains both the date and the time, e.g. 2019-08-20 10:22:34 .
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;
you do not need dual.sysdate - you can just reference sysdate.
select sysdate-staff.startdatefrom from staff
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With