SQL Help me

[Mian Umar]

Hello
I have a problem with SQL Query? I want to get the full time period of an Employee who works in an Industry. I need to calculate time like
12 years 11 months 2 weeks 4 days 10 hours 47 minutes and 50 seconds.
Whats the query?
i am waiting
i am very thankfull to you!
Best regards
Mian Umar

[sridhar_423]

what is the table structure?
and what is the query you have tried to calculate the time?

[Mian Umar]

Brother I dont have any table. Its just an assignment Only need Query to calculate time

[sridhar_423]

for calculating the time period, u need to have atleast a start date .. difference between start date and todays date. . how are passing the date.. i mean in which format?

Ps: I dont like the way ur addressing me. I dont care even if you dont address me at all. pls dont repeat that. U have a problem and want some solution and if possible i'll give..

[sridhar_423]

months .. i doubt tat.. bcos u'll be having 30 and 31 days .. i came up with this query.. which displays Years, Weeks, Days, Mins and secs.. but MONTHS on the assumption that each will have 30 days
SELECT
FLOOR(total/365) AS YEARS,
FLOOR(MOD(total,365)/30) AS MONTHS,
FLOOR(MOD(MOD(total,365),30)/7) AS WEEKS,
FLOOR(MOD(MOD(MOD(total,365),30),7)) AS DAYS,
FLOOR(fraction*24) AS HOURS,
FLOOR((fraction*24-FLOOR(fraction*24))*60) AS MINUTES,
FLOOR((fraction*1440-FLOOR(fraction*1440))*60) AS SECONDS
FROM
(SELECT (SYSDATE-TO_DATE('30-08-2005 00:00:00','dd-mm-yyyy hh24:mi:ss')) total,
TO_NUMBER(SUBSTR(SYSDATE-TO_DATE('30-09-2006 00:00:00','dd-mm-yyyy hh24:mi:ss'),
INSTR((SYSDATE-TO_DATE('30-09-2006 00:00:00','dd-mm-yyyy hh24:mi:ss')),'.'))) fraction FROM dual)

[Mian Umar]

Thanks very Much
sridhar_423

[sridhar_423]

One more thing I would like to remind you is to ask your questions/doubts here itself. This is for the benefit of all. Dont expect anyone to give u a solution in a private message. And the chance that your doubt gets cleared is high over here as the number of eyes on your question will be more if its placed here rather than in a priv. mesg.

Cheers,
Sridhar

[Mian Umar]

To: sridhar_423
Ok Brother i Understand. One thing to tell u I am NEW at here thats y i make these mistakes now I BE CAREFULL

[sridhar_423]

Ps: I dont like the way ur addressing me. I dont care even if you dont address me at all. pls dont repeat that. U have a problem and want some solution and if possible i'll give..
can't u c tat?

[Mian Umar]

I say Sorry na Sorry Again