Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-21
13
Medium Priority
?
1,831 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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 2000 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview
Suggested Courses

971 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