Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Location
    USA
    Posts
    33

    Unanswered: how to add time to date field

    hi all,

    I have a date field (with date and time stored). In another
    field there is hours and min stored. Now in the sql query
    I have to add the time stored in this field with the date and
    time field, and the same should be compared to sysdate and time to
    find the time difference.

    Following is the structure.
    Statistics_Detail
    "Tran_ID" number(10,0),
    "Name" varchar2(50),
    "From_Date Date,
    "TimeNext number (5,2)


    Data in Statistics_Detail
    "Tran_ID" "Name" "From_Date" "TimeNext"
    1 State1 29-SEP-2004 3.11 2.5
    2 State1 29-SEP-2004 2.10 3
    3 State1 29-SEP-2004 5.05 4.40


    TimeNext values are:
    2.3 = 2 hours and 30 min
    3 = 3 hours
    4.40 = 4 hours and 40 min

    in the sql query it should add
    Statistics_Detail.From_Date+Statistics_Detail.Time Next
    and should be compared with sysdate.


    thanx.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That's a strange way to do it: the number 2.3 represents 2 hours and 30 minutes? Oh well...

    Separate the hours and minutes, convert to days, then add together:

    trunc(timenext)/24 + (timenext-trunc(timenext))/24/60

    This can then be added to the from_date to the the next date and time.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Well, first of all, I have to say that storing an hour like this is not a good idea.

    This being said, this should give you what you want :
    Code:
    SELECT SYSDATE - (from_date + (TO_NUMBER(SUBSTR(TO_CHAR(time_next, '09.99'),1,2)) / 24) + (TO_NUMBER(SUBSTR(TO_CHAR(time_next, '09.99'),4,2)) / 1440)) from Statistics_Detail;
    The result is in days, and decimal : like 1.5 for 1 day and a half, 0,2 for 4:48... Not very nice, but you can convert it using the functions I already used if you want a nicer display, depending on how you want to use it.

    Hope that helps.

    Regards,

    RBARAER

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Well, Tony, I must admit that using TRUNC is more elegant than TO_NUMBER(SUBSTR(TO_CHAR()))...

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    one job I had needed to store a field for only hours and minutes to be used
    inside the application to compute different date/time calculations.
    what the developers decided to do was to store a date field but ignore the
    month/day/year itself and use the time that was stored only.

    so if they wanted to store 2 hours 50 minutes the date field would look like:
    10/20/2004 02:50:00

    calculations would obviously only use the time.
    at least this way you are always comparing date-time and not converting numbers to dates, etc.

    ps: don't ask me what they did if the time elapsed was greater than 24 hours!
    I don't think that was ever a possibility but you could easily just add a day or
    something.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since 9i we now have INTERVAL DAY TO SECOND for this kind of thing. Not that I have ever used it yet.

Posting Permissions

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