Converting Varchar AM/PM with expression

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 2005 Forums

Analysis Server and Reporting Services (2005)

Converting Varchar AM/PM with expression

Author

Topic

jhaney
Starting Member

33 Posts

Posted - 2010-12-22 : 09:40:36

I currently have 2 columns one is a start time and one is an end time. I need to subtract the start time form the end time and then average the results. here is an example of the data.

End Start
6:50 PM 6:43 PM
6:28 PM 6:14 PM
6:05 PM 5:48 PM
6:05 PM 5:50 PM
5:17 PM 5:17 PM
5:17 PM 5:17 PM

Using this expression I can get the following results

=cdate(Fields!END.Value) - cdate(Fields!START.Value)

End Start Results
6:50 PM 6:43 PM 00:02:00
6:28 PM 6:14 PM 00:14:00
6:05 PM 5:48 PM 00:17:00
6:05 PM 5:50 PM 00:15:00

I tried this expression for the average of the group
=Avg(cdate(Fields!END.Value) - cdate(Fields!START.Value))

It give me this 7200000000 instead of 00:12:00

I can do the math and get the minutes but I would like to get it in 24 hr format since some of my results will be more than 60 minutes.

Any help on this would be great.

Thanks

Joshua

jhaney
Starting Member

33 Posts

Posted - 2011-04-12 : 11:28:38

Anyone have any ideas on this?

Thanks in advance

Joshua

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources