Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Unanswered: Oracle Time Query

    Hello Experts,

    I need a query. I have a table like:


    Id Entrytime LeaveTime


    USER_ID ADATE_TIME DDATE_TIM
    --------------- ------------ ------------
    E14 27-DEC-04 27-DEC-04
    E16 27-DEC-04 27-DEC-04
    E3 27-DEC-04 27-DEC-04

    I was successfull to view te time by :
    select USER_ID,
    To_Char(ADate_Time,'HH24:MIS AM'),
    To_Char(dDate_Time,'HH24:MIS AM'),
    From tbl_attendence
    /

    Output:

    USER_ID TO_CHAR(ADA TO_CHAR(DDA
    -------------------- ----------- -----------
    E14 10:00:34 AM 10:01:46 AM
    E16 02:35:29 AM 03:22:27 AM
    E3 03:21:18 AM 03:22:00 AM

    Now my aim is to find the working period by nyThen I tried to issue this SQL to measure working hour by:
    (dDate_Time - ADate_Time)

    How can i get that? Plz help me.
    I already Tried:

    select USER_ID,
    To_Char(ADate_Time,'HH24:MIS AM'),
    To_Char(dDate_Time,'HH24:MIS AM'),
    To_Char(dDate_Time,'HH24:MIS AM') - To_Char(ADate_Time,'HH24:MIS AM') as "Hour"
    From tbl_attendence;

    But it shows error..Plz help me....l
    Last edited by Mahfuz; 12-27-04 at 06:01.
    hello

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This should work :

    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time - ADate_Time, 'HH24:MI:SS AM') as "Hour"
    From tbl_attendence;
    Regards,

    RBARAER

  3. #3
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Thnaks RBARAER but i faced Error ...

    I faced ur query but faced this error

    1 select USER_ID,
    2 To_Char(ADate_Time, 'HH24:MIS AM'),
    3 To_Char(dDate_Time, 'HH24:MIS AM'),
    4 To_Char(dDate_Time - ADate_Time, 'HH24:MIS AM') as "Hour"
    5* from tbl_attendence
    6 /
    To_Char(dDate_Time - ADate_Time, 'HH24:MIS AM') as "Hour"
    *
    ERROR at line 4:
    ORA-01481: invalid number format model

    **************************
    Pls help more
    hello

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    OK, sorry. Datetime - Datetime is a number...

    Try this :
    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(To_Date(To_Number(To_Char(dDate_Time, 'SSSSS')) - To_Number(To_Char(ADate_Time, 'SSSSS')), 'SSSSS'), 'HH24:MI:SS AM') as "Hour"
    From tbl_attendence;
    The 'SSSSS' format converts to the number of seconds since midnight. This only works if the two datetime fields concern the same day, but it seems to be the case for you.

    Regards,

    RBARAER

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    on line 4, do not use to_char. the result from using minus "-" will show the difference
    in days. To convert that to hours, multiply by 24.

    PHP Code:
    select user_id,
        
    to_char(adate_time,'hh24:mi:ss am'),
        
    to_char(ddate_time,'hh24:mi:ss am'),
        (
    ddate_time adate_time)*24
    from tbl_attendence

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Smile Special Thanks Goes To You RBARAER

    Thanks RBARAER. I got My Solution From You. Happy X-mas. Take care.
    hello

  7. #7
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You're welcome Mahfuz, I'm glad I could help you.

    The_Duck, your solution only works if adate_time and ddate_time were not on the same day, and you would only have a 24 hours precision. I think in this case, both dates were concerning the same day, and Mahfuz wanted a difference with seconds precision. Hence my solution. Of course there may be a better one.

    Best regards,

    RBARAER

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    If you want second resolution use Ducks solution but multiply by 86400 (seconds in a day).

    Alan

  9. #9
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    You're right Alan, so with the proper formatting, that would become :

    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(To_Date((dDate_Time - ADate_Time) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Hour"
    From tbl_attendence;
    Which is a little simpler as well as more elegant than my first solution.

    Might be better for you Mahfuz.

    Regards,

    RBARAER

  10. #10
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Dear RBARAER I need more From U my friend

    Dear RBARAER

    I am already grtfull to u and hope u will also help me for my this query
    I got working hour as u suggest by ur query. Now i need another query
    Like:

    My Office Working hour is : 9:00 AM to 5:00 Pm (Total 8 hour in a day)

    But if any employee enter office before 9:00 AM i mean at 8:00 AM or
    Stay more than 1 hour more than 5:00 PM like 6:00Pm
    His working hour Will be (6:00PM - 8:00AM) = 10 hour which i can find by ur previous query
    but now i need to show data like.



    User_Id ADate_time DDate_Time Hour Extar_Hour
    ------- ---------- ---------- ---- ----------
    E1 8:00AM 6:00PM 10 2



    my question is how can i minus 8 hour From (DDate_time - ADate_Time) - 8
    => (6:00PM - 8:00AM )
    => 10 - 8
    => 2 Hour
    hello

  11. #11
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    8 hours is 1/3 day, so simply add "extra" like this :
    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(To_Date((dDate_Time - ADate_Time) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Hour",
    To_Char(To_Date((dDate_Time - ADate_Time - 1/3) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Extra"
    From tbl_attendence;
    In case your working day may be more than 8 hours in the future, you could add a column "working_hours" with value 8, or possibly different values for each employee, and then execute :
    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(To_Date((dDate_Time - ADate_Time) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Hour",
    To_Char(To_Date((dDate_Time - ADate_Time - working_hours) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Extra"
    From tbl_attendence;
    Of course, it's up to you to determine if this value may be different for each employee and/or it may evolve in the future.

    Best Regards,

    RBARAER

  12. #12
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    Question Thanks RBARAER I need ur Help again...Please

    Thanks RBARAER I issued ur this query but its shows me...

    SQL> select USER_ID,
    2 To_Char(ADate_Time, 'HH24:MIS AM'),
    3 To_Char(dDate_Time, 'HH24:MIS AM'),
    4 To_Char(To_Date((dDate_Time - ADate_Time) * 86400, 'SSSSS'), 'HH24:MIS AM') as "Hour",
    5 To_Char(To_Date((dDate_Time - ADate_Time - 1/3) * 86400, 'SSSSS'), 'HH24:MIS AM') as "Extra"
    6 From tbl_attendence;
    ERROR:
    ORA-01830: date format picture ends before converting entire input string

    no rows selected

    Please reply me ...
    hello

  13. #13
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This will work :
    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(To_Date((dDate_Time - ADate_Time) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Hour",
    To_Char(To_Date((dDate_Time - (ADate_Time + 1/3)) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Extra"
    From tbl_attendence;
    And for the second solution :
    Code:
    select USER_ID,
    To_Char(ADate_Time, 'HH24:MI:SS AM'),
    To_Char(dDate_Time, 'HH24:MI:SS AM'),
    To_Char(To_Date((dDate_Time - ADate_Time) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Hour",
    To_Char(To_Date((dDate_Time - (ADate_Time + working_hours)) * 86400, 'SSSSS'), 'HH24:MI:SS AM') as "Extra"
    From tbl_attendence;
    Best Regards & Happy New Year !

    RBARAER

  14. #14
    Join Date
    Nov 2003
    Location
    Dhaka,Bangladesh
    Posts
    86

    RBARAER...My Bad Luck

    My bad lack It shows me again Error message. I am using Oracle9i. Is there any problem in my oracle RBARAER? I am confusing really...Sorry for again and again knocking u and ur important time. I am already greatfull to u my friend
    RBAREAR. Your Last 2 queries showed me the same error message....

    ERROR at line 4:
    ORA-01830: date format picture ends before converting entire input string

    Anyway, This Happy new year make ur Life Happiest my Friend.

    mahfuz
    hello

  15. #15
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    It works for me and i'm using 9i too... could you please check again ? (I've created a table like yours, and just copy/paste the query with 1/3 I gave you in my last post, and it works like a charm)

    Regards,

    RBARAER

Posting Permissions

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