Jump to content

How many days from today.


causarius@gmail.com
 Share

Recommended Posts

I'm using Oracle 10g XE. I've looked all over for this but I can't seem to find the exact answer I'm looking for. I've seen things that "work" like this:

TRUNC(sysdate) - TRUNC(emp_hiredate)
But it doesn't work in a way that I understand or in a way I can format the way I want. The question goes like this:
Write a query that will list the employee number, employee last name, employee first name, employee hire date,
a computation of the number of years since the employee was hired up to the date you run the query and the query run date
. List the computed number of years as YEARS EMPLOYED and the query run date as QUERY DATE.
I don't understand how to do this. So far, this is what I have.
SELECT emp_num, emp_lname, emp_fname, emp_hiredate
FROM employee
I know it isn't much, but I just don't understand how to do this computation. I remember my instructor saying something about needing to divide by 365 to get the correct answer, but other than that I am clueless.Any help would be greatly appreciated.
Link to comment
Share on other sites

If the two dates are actually stored as dates in the DB, you can let Oracle do the computation by actually specifying it at the top (before FROM), like:

SELECT emp_num, emp_lname, emp_fname, emp_hiredate, YEARS(CURDATE() - emp_hiredate) AS yearsFROM employee

(this is for MySQL though... if Oracle doesn't use the exact same functions, it probably has similar ones)

Link to comment
Share on other sites

I didn't know you could save a calculation that's made in a query. How would you do that?

Link to comment
Share on other sites

I didn't know you could save a calculation that's made in a query. How would you do that?
Just think of the stuff between "SELECT" and "FROM" as if it is the body of a PHP loop - what you have there is applied for every matching entry, with "matching entries" being determined by the rest of the query. Whatever you can (and need to) calculate within a PHP loop, you can calculate there instead IF the DB engine supports an appropriate function (which most DB engines support for dates).
Link to comment
Share on other sites

How do you save the value that's been calculated. Per the first post, causarius needs to know how many years each employee worked. I understand how to used that as a selector, but what if he wants to summarize that info in a table? I can image saving that calculation in in a cell that's there to receive it in a separate UPDATE, but I didn't think you can do a UPDATE and a SELECT in the same query.Is something like that possible?

Link to comment
Share on other sites

Like I said, this is applied to every matching entry, i.e. every employee (separately). Computations are lost between employees (imagine a PHP loop again. Now imagine that the value of every variable is lost at the next iteration of the loop... you know, like the pseudo variable in foreach; That's what I mean with "between employees"), but they are applied on each. For example, the emp_hiredate is initially the first employee (and the computation is based on that value), then the second employee and so on, just like how specifying "emp_lname" would first output the first last name, then the second last name and so on.You don't need to "save" it. The calculates result just becomes part of the result set, and is lost afterwards.

Link to comment
Share on other sites

You can access the result of the expression with the alias that is given with the AS keyword. SELECT expression AS name In PHP, you would access the result of the expression as $row['name']

  • Like 1
Link to comment
Share on other sites

I'm shocked to see so much activity and I want to thank everyone for contributing. I have a lot of other school work and I haven't been able to get back to this question yet, but I just wanted to let you know I didn't just post and run, and that all your assistance is not in vain.As far as the PHP stuff goes...I'm a novice with SQL, I don't know jack squat about PHP. I haven't had a chance to try it, but this seems like it would be close to what I'm looking for:

SELECT emp_num, emp_lname, emp_fname, emp_hiredate, YEARS(CURDATE() - emp_hiredate) AS yearsFROM employee

But instead of CURDATE, it would be SYSDATE.When I get a chance I will give it a shot and report back.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...