| very vrey urgent - subtract time [message #7271] |
Sun, 01 June 2003 09:34  |
shareef
Messages: 72
Registered: November 2002
|
Member |
|
|
hi sql export,
i want to calculate time (hour and minutes) from two columns of date data type. i have given query like this,
SELECT TO_DATE(END_TIME,'HH:MI') - TO_DATE(START_TIME,'HH:MI') FROM WORK;
it is giving a error called
ERROR:
ORA-01849: hour must be between 1 and 12
if i am giving to_char() function, it is giving error called
ERROR:
ORA-01722: invalid number
if i am giving to_date() with the format as 'dd-mm-yy hh:mi', it is calculating time if it is more than 24 hours other wise it is showing 0.
Even minutes also i want to calculat. please help me i will be very much thanks full.
thanks in advance
hari
|
|
|
|
| Re: very vrey urgent - subtract time [message #7274 is a reply to message #7271] |
Mon, 02 June 2003 02:26  |
Anand
Messages: 161
Registered: August 1999
|
Senior Member |
|
|
i havent but you can try,
Store hr and min in 2 variables each for your start and end time like
to_Date(yourDate,'dd-mm-yy hh24') for Hours
to_Date(yourDate,'dd-mm-yy mi') for Mins
separatly .
|
|
|
|
| Re: very vrey urgent - subtract time [message #7292 is a reply to message #7271] |
Mon, 02 June 2003 19:53 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Since your columns are both of date data type, there is no need to use to_date or to_char, just subtract one from the other to get the number of days, then do your calcuations on the number of days to extract and format the hours and minutes. If you multiply the number of days by 24, you get the number of hours. Use the TRUNC of that for your hours and the remainder when divided by 1 (MOD) for your minutes, then round and format that. If you search these forums, you may find various similar methods.
SELECT TRUNC ((end_time - start_time) * 24) || ':'
|| TO_CHAR (ROUND ((MOD ((end_time - start_time) * 24, 1)) * 60), 'fm09')
FROM work
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
