Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding 0's to time in SSRS Expression Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 12:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 24, 2014 11:06 AM
Points: 109, Visits: 215
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
Post #1515745
Posted Tuesday, November 19, 2013 1:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:57 PM
Points: 1,660, Visits: 5,223
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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1515749
    Posted Tuesday, November 19, 2013 2:17 PM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, February 24, 2014 11:06 AM
    Points: 109, Visits: 215
    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
    Post #1515780
    Posted Tuesday, November 19, 2013 4:11 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 4:57 PM
    Points: 1,660, Visits: 5,223
    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


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1515830
    Posted Wednesday, November 20, 2013 6:57 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, February 24, 2014 11:06 AM
    Points: 109, Visits: 215
    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 :)
    Post #1516035
    Posted Wednesday, November 20, 2013 7:59 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Today @ 5:08 AM
    Points: 391, Visits: 703
    In SQL you could just do this:
    CAST('1:2:3' AS DATETIME)

    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
    Post #1516071
    Posted Wednesday, November 20, 2013 8:23 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Monday, February 24, 2014 11:06 AM
    Points: 109, Visits: 215
    Thanks! I am new to SSRS, so simple things like this are a bit challenging. Thanks very much for your time :)
    Post #1516076
    Posted Wednesday, November 20, 2013 9:08 AM
    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Today @ 5:08 AM
    Points: 391, Visits: 703
    You're welcome Dave




    I'm on LinkedIn
    Post #1516099
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse