SQL (server) date conversion

Question

I want to convert the run duration (from msdb..sysjobhistory) to an elapsed seconds figure. The duration is stored as an integer where for example

13     means 13 secs
213    means 2 mins and 13 secs = 133 secs
310213 means 31 hours, 2 mins and 13 secs  = 111,733 secs

Is there any neat way of doing this?

It's SQL Server 2000 but I would prefer generic SQL

Answer 1

I don't think there is any neat ANSI way to do this, or even a neat way using SQL Server 2000 extensions. You can do it as follows:

(duration % 100) +
(((duration / 100) % 100) * 60) + 
(duration / 10000) * 60 * 60