Time Formatting

[rute67]

I am trying to format seconds into hours. I am using the following:

dateadd(ss,sum(cast(duration as int)) ,'12/31/2099')

this gives me a value of 12/31/2099 00:00:00.000.

the problem falls when the sum of the seconds falls greater than 24 hours. instead of getting a value like 12/31/2099 27:49:15.000, I get 01/01/2100 03:49:15.000.

Does anybody know how to get the value in hours only. I have tried to convert to get the 27 but I still got the wrong time. It always adds a day to the time instead of just giving me the value of hours the time represents.

Thanks for your help.

 

[SQLBill]

You aren't going to be able to do it using the DATETIME datatype. This isn't a valid time: 27:49:15.000

If that is really what you want, you are going to have to use VARCHAR or CHAR to hold the data.

-SQLBill

 

[rute67]

I have tried both and all I get is the time but not the correct one. if I the time was 27:13:30.000 and I use char or varchar I get 03:13:30.000.

 

[tlbroadbent]

You can get hours by dividing seconds by 3600.

cast(duration as int)/3600

Of course this truncates the value so that 27:59:59 will be truncated to 27. If you want to round, use the following.

Round(1.0 * @dur / 3600, 0)


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[rute67]

that will only give me a whole number ex: 27, 23, 24...

But the problem is that I need to get the entire number.

EX: 26:14:29.000 or 23:34:09 something like that.

 

[tlbroadbent]

Just need to do the math. Here are two solutions. You may need to make modifications depending on your data.

--Compute hours by dividing seconds by 3600
--Use the modulo operator to get the reamining seconds.
--Divide the remaining seconds by 60 to get minutes
--Find the remaining seconds
--Convert all to strings, concatenate and replace spaces with zeros
Select Str(Duration/3600, 3)+':'+replace(str((Duration % 3600)/60, 2)+':'+str((Duration % 3600) % 60, 2),' ','0')

--Find the hours
--Find the remaining seconds
--Use data add to get the minutes and seconds in hh:mm:ss format
--Stuff the hours into the first 2 characters of the result
Select Stuff(convert(char(8), Dateadd(s,Duration % 3600,'1900-01-01'), 8),1,2,Str(Duration/3600, 3))

There are other ways to solve the problem. Maybe someone can post a simpler function.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm