How to do this in SSRS using expressions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

How to do this in SSRS using expressions

Author

Topic

sanghavi17389
Starting Member

26 Posts

Posted - 2012-05-18 : 10:07:28

My purpose is to find out time difference.

eg:

time1 = 22:32:34 = ((22)*60*60) + (32*60) + 34 = 81154
time2 = 3:03:46 = (3*60*60) + (3*60) + 46 = 11026

time1-time2 = 81154 - 11026 = 70128

CONVERT DIFFERENCE TO H:M:S

1) Convert to minutes and seconds
70128 / 60 = 1168 //integer division, number of minutes
70128 % 60 = 48 //modulus, number of seconds left

2) Convert minutes to hours
1168 / 60 = 19 //integer division, number of hours
1168 % 60 = 28 //modulus, remaining minutes

Time difference is then 19 hrs, 28 minutes, 48 seconds.

harshal sanghavi

edit: moved to proper forum

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2012-05-18 : 18:02:45

Does this help?

TheTime = convert(varchar, convert(int,floor(TimeCol)))+ ':'+ replicate('0',(2 - len(convert (varchar, convert(int,(TimeCol - floor(TimeCol)) * 60.0)))))+ convert (varchar, convert(int,(TimeCol - floor(TimeCol)) * 60.0))

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-18 : 18:19:35

This works in the expression builder:

=DateAdd("s",DateDiff("s",Fields!date2.Value,Fields!date1.Value),"1/1/1900")

You'll have to set a number format of Time to get the correct display. The field values also have to be time data type, or if they're datetime, have the same date portion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources