I have a table with 3 columns:
end_t time(0), and duration time(0)
I have start_t value as '17:31:10' , end_t as '17:39:07'
I want to calculate duration as end_t-start_t => 17:39:07-17:31:10
I am getting the value as '00:07:97'
but 97 is invalid seconds...can anyone please help me in getting the duration also in 'HH:MM:SS' format
sel (time'17:39:07' - time'17:31:10') hour to second;
to be more precise, u can also use format commands to format ur answerset accordingly:-
sel (time'17:39:07' - time'17:31:10') hour to second(0);
First, verify that your column is really a TIME field. (The ODBC parser may substitute FLOAT when you specify TIME if the middle letter in DateTimeFormat is I.) Use SHOW TABLE or HELP COLUMN or query the DBC tables directly. Also note that the difference of two TIME fields will be an INTERVAL, not another TIME.
@Fred : yes. But, my question is..As to how to save this interval to one more field duration which is a time(0) datatype.
@mtlrsk: I do not want to hard code the time value...
Please help me...I have to get the duration value in Time(0) format.
But duration would be an INTERVAL datatype. It's logically incorrect to assign an INTERVAL value to a TIME field. If you absolutely insist on storing the result as TIME(0) instead of the proper type, you can add the INTERVAL to TIME constant of midnight. Or convert to Character and back, since INTERVAL HOUR(2) TO SECOND and TIME look alike in character form.