Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Windza
Yak Posting Veteran
61 Posts |
 Posted - 2009-07-30 : 03:10:23
|
| Let me state first that I have most definitely searched numerous posts on the conversion of seconds values to hh:mm:ss values... none of them are particularly helpful in my current situation.I have fields with a time period displayed as seconds that I wish to display in Sql Report Builder 2.0 (RB2) as hh:mm:ss. If I format the fields within the data set query to be hh:mm:ss, RB2 will not accept calcs (sum, avg etc.) due to char type... if I leave the format as seconds I cannot seem to achieve the hh:mm:ss formatting in RB2.I've had much better luck in Excel perviously where you simply divide the seconds field by 86400 and format the cell as [h]:mm:ss.Is it possible to achieve something like this in RB2?Your help is appreciated... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-30 : 13:50:47
|
| you need to first convert the seconds value to a datetime. you can use like thisselect convert(varchar(8),dateadd(ss,yoursecondsvaluefield,0),108) from table |
 |
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2009-08-03 : 02:12:36
|
quote:
Originally posted by visakh16
you need to first convert the seconds value to a datetime. you can use like thisselect convert(varchar(8),dateadd(ss,yoursecondsvaluefield,0),108) from table
I have already done this but still get an error with aggregate expressions (e.g. SUM() fails).Is there any obvious reason for SUM() failing?Keep in mind this is the SUM function from within RB2 (appears to be different) |
|
|
|
Mangal Pardeshi
Posting Yak Master
110 Posts |
Posted - 2009-08-03 : 07:45:56
|
| First do the SUM and then change the format.Mangal Pardeshihttp://mangalpardeshi.blogspot.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-08-04 : 03:05:03
|
quote:
Originally posted by Windza
quote:
Originally posted by visakh16
you need to first convert the seconds value to a datetime. you can use like thisselect convert(varchar(8),dateadd(ss,yoursecondsvaluefield,0),108) from table
I have already done this but still get an error with aggregate expressions (e.g. SUM() fails).Is there any obvious reason for SUM() failing?Keep in mind this is the SUM function from within RB2 (appears to be different)
you cant sum over varchar field. so do the sum and then do conversion |
|
|
|
Windza
Yak Posting Veteran
61 Posts |
Posted - 2009-08-16 : 21:45:01
|
| Thanks ppl... Vis was right and I've now managed to format it how I need..Cheers, |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-08-17 : 07:26:33
|
| welcome |
|
|
|
|
|
|
|
|
Another seconds to hh:mm:ss Question !