3

I have a table in my report, where I have columns of datatype Time(7).

Now I have problems formatting them correctly in Reporting Services 2008.

If I set the format of the expression to HH:mm it does still display 11:12:000 !

I want to get only the hours and minutes! like 11:12

It looks like RS does not know the format. None of the following does work: 

=Hour(Fields!MyTime.Value)

=CDate(Fields!MyTime.Value)

Both throw an error. I think it propably does format it as plain text?

Thanks for your assistance

Edit:

I use SQL Server 2008 R2 Express as the database. (so I include the DataSource in the report, because Reporting Services in SQL Server Express does not allow to use shared DataSources.)

The Solution (Thanks Mark Bannister):

=Today() + Fields!MyTime.Value

Then you can use the common formatting used for datetime values!

SwissCoder
  • 2,514
  • 4
  • 28
  • 40
  • What datasource (SQL/other, SQL dialect, version, etc) are you using? –  Oct 04 '10 at 09:55

2 Answers2

4

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

  • 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 Oct 05 '10 at 09: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. –  Oct 05 '10 at 09:54
  • 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 Oct 05 '10 at 13:07
  • time1 + time2 does work in the report -> but I lose the correct formatting as HH:mm! – SwissCoder Oct 05 '10 at 13:08
  • 1
    @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). –  Oct 05 '10 at 13:37
  • 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 Oct 05 '10 at 15:04
  • My Problem is not on the SQL-Server side! Its in the formatting of the Report. – SwissCoder Oct 05 '10 at 15:05
  • @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. –  Oct 05 '10 at 15:29
  • Hi Mark Bannister, that last comment made it clear to me at second reading! Thank you a lot! – SwissCoder Oct 06 '10 at 08:05
1

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
D.S.
  • 1,413
  • 2
  • 16
  • 26
  • 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 Oct 06 '10 at 07:58