1. Registered User
Join Date
Dec 2003
Location
USA
Posts
33

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. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
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. Registered User
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. Registered User
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. Registered User
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.

6. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171