convert int to mins, secs 2

[MaffewW]

We have an int field, call length in seconds. I'm sure its possible, but I cant think how to get it into hours/mins/secs or just mins/secs

I'm trying to do some calculations on it, an example

select convert(decimal(8,2),sum(convert(decimal,(length)))/60)/count(rowid) as 'avg call length (mins)' from callhistory

which gives me length in minutes, though obviously its in decimal. Has anyone got a pointer. (PS I know it'd be easy in VB or Crystal or something though this is going to be output straight to a static html web page so I need to try and do a SQL solution)

Cheers

Matt

Brighton, UK

 

[billchris]

Matt,

You can use DateAdd to add seconds to 0 and convert the result to a string in the format hh:mm:ss.

  select
    Convert(nchar(8), DateAdd(s,Avg(length),0),8) as 'avg call length'
  from
    callHistory

This will work as long as the avg call length is less than 13 hours, because format style 8 is a 12-hour format.

If the data is being output on a web page, can you use vbscript to do further formatting on the result? If so, use FormatDateTime on 'avg call length' with the vbShortTime format for a 24-hour format.

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal

 

[MaffewW]

cheers billchris knew there was a way just couldnt think of it

Matt

Brighton, UK

 

[MaffewW]

oh yeah, cant do any formatting outside SQL at the moment though all values should be less than 12 hours, waiting to employ a Web developer after our last one abandoned ship

Matt

Brighton, UK

 

[MaffewW]

would anyone know how to (with SQL) convert from seconds (int field) to hh:mm:ss when the amount of hours is over 12 or 24 hours?

Matt

Brighton, UK

 

[sunila7]

Hi Matt,

Try this... Hope this helps...

select convert(varchar(20),dateadd(ss,3453453, '1/1/1900'),108)


Sunil

 

[MaffewW]

what does the additional 3453453 seconds represent

Matt

Brighton, UK

 

[sunila7]

Hi,

If u have a field where no of seconds is stored which u want to convert to the hh:mm:ss format. u can try this

Select convert(varchar(20),dateadd(ss,secFld, '1/1/1900'),108) From TBL


Sunil

 

[MaffewW]

Hi Sunil, thanks for answer, though I think that only works if the value in seconds is less than 24 hours. eg I have a totaltalktime field which is 5608697 seconds and should be about 1557.97 hours (dec) where with your code I get 21:58:17

If you've got any other idea let me know

cheers


Matt

Brighton, UK

 

[sunila7]

Hi Matt,

Try this .... Does this look right...

select convert(varchar(10),datediff(hh,'1/1/1900',dateadd(ss,5608697 , '1/1/1900'))) + ':' +
convert(varchar(10),datediff(mi,'1/1/1900',dateadd(ss,5608697%(60*60) , '1/1/1900'))) + ':' +
convert(varchar(10),datediff(ss,'1/1/1900',dateadd(ss,(5608697%(60*60))%60 , '1/1/1900')))

select Convert(varchar(10),5608697/(60*60))+ ':' + convert(varchar(10),(5608697 % (60*60))/60) + ':' + Convert(varchar(10),(5608697 % (60*60))%60)


Sunil

 

[MaffewW]

certainly does thanks Sunil. worth a star

Matt

Brighton, UK

 

[SQLSister]

If you are using SQL Server 200 suggest you put this code in a user defined function as you will probably want o use it multiple places.