Calculating time interval (from a string)

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)

Calculating time interval (from a string)

Author

Topic

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-07-30 : 16:00:12

I need to summarize a column with time intervals (total duration of conversations). Data type of the field in my database is string (varchar). I tried to convert the string to datetime, but I always got same format: 1900-01-01 hh:mm:ss. I tried to convert with 108 or 8, it doesn't matter what I write inside the convert function, 1900-01-01 is still there. I can get ride of 1900-01-01 if I change placeholder properties (formatting number and date) in the report but I still can't summarize the column. I even tried with following expression =System.TimeSpan.FromTicks(Sum(Fields!Total_Duration_of_Conversation.Value)) but I just got #error. I even tried to write some UDF in the database (for TimeOnly) but I still can't use SUM either in the query or in the report. Any suggestions how to solve this, I just wanna have total and grand total, it doesn't matter if I do it in my database or in the report. Please help!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-30 : 17:16:29

Since you are using a string datatype to store a numeric value, you have put yourself into a difficult position.
First lesson in database architecture is to use proper datatype.

Now, how are you storing the time values? Which format?

N 56°04'39.26"
E 12°55'05.63"

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-07-30 : 17:50:10

I am not the one who builded the database from the begining, I am the one who makes reports (hopefully).

The data is stored as hh:mm:ss (in a string).

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-30 : 22:02:53

T-SQL does not have support for the equivalent of .Net's TimeSpan class to represent time intervals. So if you want to do it in T-SQL, you have to improvise.

There is some good information here with various possibilities. In your case, you could do something like what I am showing below, which would give you the duration in days, hours, minutes and seconds:

SELECT
   dseconds/(24*60*60) AS Days,
   dseconds/(60*60)%24 AS Hours,
   dseconds/(60)%60 AS Minutes,
   dseconds%60 AS Seconds
      
FROM
( 
   SELECT
      SUM
      (
         ( CAST( DATEPART(second,timeCol) AS BIGINT)
         + 60*(DATEPART(minute,timeCol) 
         + 60*DATEPART(hour,timeCol)))
      ) AS dseconds

   FROM
      YourTable
)p

Here, I am assuming that the data is stored in a table called "YourTable" (great name for a table, isn't it?

) and that the column name is timeCol. If it is stored as string in the format you specified, it can be implicitly converted to DATETIME data type. The DATEPART functions in the query above rely on being able to do that conversion.

Alternatively:

SELECT
   dseconds/(24*60*60) AS Days,
   dseconds/(60*60)%24 AS Hours,
   dseconds/(60)%60 AS Minutes,
   dseconds%60 AS Seconds  
FROM
( 
   SELECT
      SUM( CAST( DATEDIFF(second,'19000101',timeCol) AS BIGINT) ) AS dseconds
   FROM
      YourTable
)p

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-07-31 : 15:54:49

Thank you for your reply! This works in a query but I don't really know how to apply it in my report. I have date parameters, so let's say you choose start and stop date, than you may get following data in the report:

Date__Time interval__Recieved calls__Total duration of conversation
1 jul__09:00-10:00_____2_________________00:05:07
_____10:00-11:00______1_________________00:02:00
Total________________3_________________#error
2 jul__09:00-10:00_____2_________________00:04:04
_____10:00-11:00______0_________________00:00:00
Total________________2_________________#error
Grand total__________5_________________#error

I hope you understand what I mean, it is difficult to illustrate a table in raw text.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-31 : 22:08:30

Even though you had posted the question in SSRS forum, it didn't dawn upon me that you were doing this in SSRS. In SSRS you can access the .Net TimeSpan structure. So you can do a summation using an expression such as this:


System.TimeSpan.FromTicks
(
   Sum
   (
       System.TimeSpan.Parse(Fields!timeCol.Value)
   )
)

This would handle all the dirty details for you such as taking care of overflow of hours into days etc. However, I am wrote this expression based on my familiarity with C# rather than with SSRS (which I am less familiar with), so if it does not work, please post back with the exact text of the error message?

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-08-01 : 02:52:12

Now I got following error:
Input string was not in a correct format.

I don't understand, because data is stored as hh:mm:ss (in a string/varchar).

Using TimeSpan would really would be the smoothest way to solve it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 07:18:09

If the data indeed is in the hh:mm:ss format, Parse method should be able to parse it. Parse is fairly flexible - it can handle most of the abuses that we throw at it the example on this page shows a few different possibilities.

You would need to debug this. I would start out by parsing a single string to see if you are doing everything right. Then, I would test the summation part by pointing your formula at a table that has just two rows.

Is it possible that some of the data is not in the hh:mm:ss format?

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-08-01 : 08:51:13

You are so right!

I had some empty fields, that's why I got an error. If there is a value in every field, than it works great. Thank you a lot!

I even tried to get ride of the error with empty fields and tried following:
=System.TimeSpan.FromTicks(Sum(iif(Fields!Total_Duration_of_Conversation.Value = nothing, TimeSpan.Zero, (System.TimeSpan.Parse(Fields!Total_Duration_of_Conversation.Value)))))

but I got error:
Value cannot be null Parameter name s.

Any idea how to solve even this one?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 08:57:56

You may need to use "is Nothing" instead of "= Nothing".


=System.TimeSpan.FromTicks(Sum(iif(Fields!Total_Duration_of_Conversation.Value is nothing, TimeSpan.Zero, (System.TimeSpan.Parse(Fields!Total_Duration_of_Conversation.Value)))))

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-08-01 : 12:29:26

It's still same error, Value cannot be null...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-01 : 13:44:37

Try changing the IIf before you do the parsing as in:


=System.TimeSpan.FromTicks
(
   Sum
   (
      System.TimeSpan.Parse(Iif(Fields!dtStr.Value is nothing,"0", Fields!dtStr.Value))
   )
)

arnesbeganovic
Starting Member

9 Posts

Posted - 2011-08-02 : 03:04:49

Yeah, that's it!! Thank you very much!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources