ansaurus

Question

Displaying Time in Reporting Services 2008

Answer 1

+1 A:

Try replacing MyTime with cast(MyTime as datetime) as MyTime in your query, and set the format of the expression to HH:mm.

Mark Bannister 2010-10-04 16:09:08

That solves the problem if I do nothing with those time values. But When I do an Addition or Subtratcion 2 of those values I still get the wrong format, because Math with Dates return a timespan I guess... But converting with CDATE(mytime1 +mytime2) doesnt work.

SwissCoder 2010-10-05 09:37:37

mytime1+mytime2 shouldn't be a valid expression anyway, as they are both time values - I can't work out what you are trying to achieve with that expression. mytime1-mytime2 would give you a time interval as the difference between the two, except I don't think SQLServer allows datetime arithmetic that way - you would have to use datediff instead. I suggest using the existing date/time functions in SQLServer, such as datepart and datediff.

Mark Bannister 2010-10-05 09:54:19

you missunderstood me. I do the subtraction off the too dates in the Report! I want to add time1 + time2 and get the total time and display it in the report. That should be possible somehow!

SwissCoder 2010-10-05 13:07:56

time1 + time2 does work in the report -> but I lose the correct formatting as HH:mm!

SwissCoder 2010-10-05 13:08:56

@SwissCoder, it sounds as though you are treating the time values as though they were time duration - my understanding is that they are actually timestamps (like datetimestamps, but with no date element).

Mark Bannister 2010-10-05 13:37:28

Thank for your quick replies. What I exactly do is: (time1 -time2)+(time3-time4). So I am adding 2 timespans.. giving my a Timespan as Result I think.. and that Resulting Timespan has the correct value. but I can not display that in the correct format.

SwissCoder 2010-10-05 15:04:36

My Problem is not on the SQL-Server side! Its in the formatting of the Report.

SwissCoder 2010-10-05 15:05:32

@SwissCoder, the DateTime format property is for datetimestamp values, not duration/timespan values. If you add your duration to a midgnight-valued date, the resultant datetime value can be formatted as HH:mm.

Mark Bannister 2010-10-05 15:29:09

Hi Mark Bannister, that last comment made it clear to me at second reading! Thank you a lot!

SwissCoder 2010-10-06 08:05:28

Answer 2

+1 A:

Try wrapping the expression in with the FORMAT() function. For example:

You have a textbox, or a datagrid/matrix with the time value in it. Edit the expression as:

format( (time1 -time2)+(time3-time4) , "HH:mm") I often use this with Datetime to "cut off" the time when displaying it. Example format(dateVal,"MM/dd/yyyy") will display 10/05/2010

Here is a little more info on it which may help out:

http://msdn.microsoft.com/en-us/library/59bz1f0h(v=VS.90).aspx

D.S. 2010-10-05 17:12:51

Hi Scott, thank you. But this not work... I think after I subtract one datetime from another datetime I get a timespan or timeoffset.. and somehow that will throw an error when trying to format it as HH:mm

SwissCoder 2010-10-06 07:58:43

ansaurus

tags:

views:

answers:

Displaying Time in Reporting Services 2008

related questions