Adding 0's to time in SSRS Expression

  • Hello,

    I have a report that is displaying time in the following format I assume by default... (1:3:28), I would like to see (1:03:28). Is there an expression in SSRS that accomplishes this?

    Also, does anyone know how many people can look at one report at the same time? Is there a limit?

    Thanks in advance!

    Dave

  • It depends a lot on the data type of the item being displayed, but if it is a date/time field, then just put a capital "T" in the "format" property of the textbox that contains it.

    See here : Standard Date and Time Format Strings

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Unfortunately, I am using SQL 2005, so it's a varchar. Here is the expression. Pardon the spaghetti code...

    =(SUM(Cint(Split(Fields!ACDTime.value,":").GetValue(0)))

    + (SUM(Cint(Split(Fields!ACDTime.Value,":").GetValue(1)))

    + Sum(Cint(split(Fields!ACDTime.Value,":").GetValue(2)))\60)\60 ).ToString

    + ":" + ((SUM(Cint(Split(Fields!ACDTime.Value,":").GetValue(1)))

    + Sum(Cint(split(Fields!ACDTime.Value,":").GetValue(2)))\60) Mod 60).ToString

    + ":" + (Sum(Cint(split(Fields!ACDTime.Value,":").GetValue(2))) Mod 60).ToString

  • Sorry, two things that need clarifying

    1. Why can't you use a datetime instead of a varchar? SQL 2005 certainly has datetime and smalldatetime?

    2. If you must use a varchar, how is it populated and from what source data type? Can it be manipulated in SQL before it gets to SSRS?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Apologies for not being clear and thanks so much for your response.

    Ok, so basically I have a time value (1:2:3) as vachar and want to ensure that the zero's will show (1:20:30). Is there a way to format this? I know you can remove the time from a date in SSRS, but how do you tell SSRS to keep the zero's?

    In regards to SQL 2005, the time datatype was not available until SQL 2008.

    Thanks

    Mr. Magoo 🙂

  • In SQL you could just do this:

    CAST('1:2:3' AS DATETIME)[/code]

    which would return "1900-01-01 01:02:03.000" and then use SSRS to extract the time only, preserving the zeros.


    I'm on LinkedIn

  • Thanks! I am new to SSRS, so simple things like this are a bit challenging. Thanks very much for your time 🙂

  • You're welcome Dave 🙂


    I'm on LinkedIn

  • Viewing 8 posts - 1 through 7 (of 7 total)

    You must be logged in to reply to this topic. Login to reply