# Thread: how to work out length of employement?

1. Registered User
Join Date
Mar 2007
Posts
12

## Unanswered: how to work out length of employement?

hi! i have table called employee. in there i have the fields

startDate Date,
lengthOfEmployement number(5),

i want to work out the lenghtOfEmployement but when i try the code

Code:
```insert into employee values(
1001, 7777, 'John', 'Smith', '12', 'Woodcock', 'Cheshunt', 'Hertfordshire',
'ER3RR3', '01233433456', '12-03-1999', round(sysdate - startdate), 100101);```
it gives me an error 'column not allowed'. how can i work out the lenghtOfEmployement within that field.
thanks!

2. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,108
Don't INSERT, UPDATE!
Code:
```UPDATE employee SET
lengthOfEmployement = ROUND(SYSDATE - startdate);```
However, storing computed values in a table is not a very good idea. "Number of days" is about to change every day and there's really no purpose in doing that.

3. Registered User
Join Date
Mar 2007
Posts
12
ok thanks. but have u got any ideas how i could improve this then? can i have it calculate only the months then. this would be more sensible.

4. Lost Boy
Join Date
Jan 2004
Location
Croatia, Europe
Posts
4,108
No, it wouldn't.

This information isn't worth much. What would you do with it in a table, anyway? Select it and display in a report? If so, calculate it when the report is run. Otherwise, you'd first have to update a table (which might take a while if a table is large) and then display the result in a report.

Once again: do not store such information in a table - calculate it whenever needed.

5. Registered User
Join Date
Jun 2004
Location
Liverpool, NY USA
Posts
2,516
Littlefoot is totally correct. When you need the years of service (for example), simply compute it during the select.

SELECT FLOOR(MONTHS_BETWEEN(SYSDATE,STARTDATE))/12) YEARS
FROM EMPLOYEE;

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•