Calulation of duration in time format.

Database

Calulation of duration in time format.

Hi,

I am having a table, which looks like below:

create table activity_time

(

start_time time(0),

end_time time(0)

);

I am inserting row in this table as below:

insert into activity_time values ('12:10:10', '12:12:15');

Now when I am firing the SQL as:

sel end_time - start_time from activity_time;

It's giving me result as '205' instead of '00:02:05'.

Can anyone please explain me how to get this calculation in HH:MM:SS format?

Thanks in advance !!

 

1 REPLY
Senior Apprentice

Re: Calulation of duration in time format.

I assume you're using SQL Assistant and an ODBC connection.

You should get an error message "5407 Invalid operation for DateTime".

Do a SHOW TABLE and check if the columns are INT instead of TIME.

To avoid this switch to .NET or change one of the following options in ODBC:

set the "DateTime Format" to "AAA" or check "Disable Parsing"

And then it's:

sel end_time - start_time hour to second from activity_time;

Dieter