SSRS 2008 - How to round time field to the nearest second

SSRS 2008

What is the easiest way of rounding a Time field to the nearest second so that I don't have time formats like 00:00:39.190000 but have 00:00:39 instead?

I have tried a format of "t" which is short time format but tha开发者_JAVA技巧t did not seem to work.

If you want to format it as a string, use CONVERT. If you want to actually truncate the fractional part of the datetime, use DATEADD to subtract it off.

declare @now datetime;
declare @floored datetime;
set @now=GETDATE();
-- get rid of the factional part by subtracting it.
-- datetime has worse than 1 msec of precision so this is exact.
-- datetime2 has 100 nsec precision; will need to use ns then.
set @floored = DATEADD(MS,-DATEPART(ms,@now),@now); 
-- output as value and as string.
-- see documentation on date and time styles. I like ODBC (120/121).
select
@now as GetDateTime,
@floored as GetDateFloored,
CONVERT(varchar,@now,120) as GetDateString, 
CONVERT(varchar,@floored,120) as GetDateFlooredAsString 

If you use the properties you can enter any format string you'd like.

If the value occupies a complete cell, you should set the format for the text box, otherwise the placeholder, and the last, worst, option is to use a formula. (=format(Fields.DetailedTime.Value, "HH:mm:ss") )

Set the format to hh:mm:ss http://msdn.microsoft.com/en-us/library/8kb3ddd4%28v=VS.80%29.aspx

The standard .net format strings work here: http://msdn.microsoft.com/en-us/library/fbxft59x%28v=VS.80%29.aspx