Solved

I have to convert the given value into the format " Days:HH:MM:SS  " in SSRS report

Posted on 2011-02-21
13
1,657 Views
Last Modified: 2012-05-11
I have to convert the value into    DDD:HH:MM:SS   format.

For example
  if I get the value 241 ,   I have to convert it into 10:01:00:00


 When > 24 hours, to use number of days

DDD -days
HH -Hours
MM -Minutes
SS -Seconds

How to do it in ssrs expression?!
0
Comment
Question by:Manikannan
  • 7
  • 6
13 Comments
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 34950550
what is the value 241, is it minutes, days or hours or seconds? based upon that we should write the function
0
 

Author Comment

by:Manikannan
ID: 34950657
Suresh,
 Its minutes
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 34951102
declare @mint int   /*  minutes */
set @mint = 3456

Select @mint,
Cast(@mint / 1440 as Varchar) + ':' +
Cast((@mint%1440) / 60 as Varchar) + ':' +
Cast(@mint % 60 as Varchar)
0
 

Author Comment

by:Manikannan
ID: 34951139
Suresh,

         I asked it in the SSRS expression , not in the sql stmt.
Is it possible to add in ssrs?!
  for ex
  DATEADD("n",Parameters!NoOfMinute.Value)
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 34951216
yes, same logic you use it in ssrs expression by concatenating it in a expression of a text box
0
 

Author Comment

by:Manikannan
ID: 34951265
How to do that suresh?!

Now in the textbox, i'm displaying the following,

=Last(Fields!I_TIME_ON_GPS.Value)


Can u tell me how to change the "I_TIME_ON_GPS.Value"  value into "DDD:HH:MM:SS"    format
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 34951376
try this in expression


CChar(Fields!I_TIME_ON_GPS.Value/1440) + ":" + CChar((DivRem(Fields!I_TIME_ON_GPS.Value,1440))/60) + ":" + CChar(DivRem(Fields!I_TIME_ON_GPS.Value,60)) + ":00"

the ":00" in last is for seconds, as the number is in minutes, you cannot divide them into seconds
0
 

Author Comment

by:Manikannan
ID: 34951485
No Suresh,

It shows the error as "wrong number of arguments" in the following part

CChar((DivRem(Fields!I_TIME_ON_GPS.Value,1440))/60)

0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 34951578
Replace DivRem(Fields!I_TIME_ON_GPS.Value,1440)   to Fields!I_TIME_ON_GPS.Value Mod 1440


and



DivRem(Fields!I_TIME_ON_GPS.Value,60) to Fields!I_TIME_ON_GPS.Value Mod 60
0
 

Author Comment

by:Manikannan
ID: 34951673
Its showing error only.

Is it working in ur side?!
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 500 total points
ID: 34952361
it should work , try to use each part at a time and then keep on adding one by one if the earlier one works
0
 

Assisted Solution

by:Manikannan
Manikannan earned 0 total points
ID: 34958157
I use like the following,

Fields!I_TIME_ON_GPS.Value \ 1440  & ":" & Fields!I_TIME_ON_GPS.Value \ 60 & ":" & Fields!I_TIME_ON_GPS.Value mod 60



Its working,  Thanks suresh for your idea.
0
 

Author Closing Comment

by:Manikannan
ID: 34990907
It helped me partially
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now