I don't think AM/PM is an issue really.  Here's the code to calc the diff in minutes; I'll leave converting those minutes to hours:mins or hours.hours as I'm pressed for...

I think the problem may be deeper than that.  Remember, once you alias a table in a query, you cannot refer to that table by its original name, but only...

You need to add ending quotes to the strings, and not specify a third parameter for the the CHARINDEX, like so:

SELECT SUBSTRING('APP4005673452-45580', CHARINDEX('-','APP4005673452-45580') + 4, 4)

Since you're already using dynamic SQL, you can directly pass the value to the SQL command:

ALTER PROCEDURE spGetList
  @ID INT
AS
--DECLARE @ID INT
DECLARE @Query...

Would need to see the query plan to know for sure what is going on, but, based solely on your description, there's a limited amount you can do.

1)...

Be sure to cluster the temp table as well, on ( TransactionGroupGUID, b.TransactionID )

I agree, we need more details and a temp table would be much better.  But you should always UPDATE an alias when doing JOIN(s) in an UPDATE.  And, if possible,...

select top (1) id
from @test T
where VendorID = @VendorID and (CompanyId = @CompanyId Or CompanyId IS NULL)
order by companyid desc

Perhaps this?:

;

WITH my_junk AS (
 SELECT CAST(my_column as varchar(7)) as my_column FROM (VALUES
 ('-1.23456'),('-1.2345'),('1.2345000'),('12.3456'),('-1.234'),('1.234'),('-12.345'),('12.345'),('-12.34'),('12.34'),('-1.23'),
 ('ab'),('x'),(''),('0'),
 ('1.23'),('-1.2'),('1.2'),('-12.3'),('12.3'),('-1'),('1'),('-12'),('12')
 ) d (my_column)
)
SELECT my_column, final_value
FROM my_junk

SQL Server provides system views that greatly assist you here.  You need to use those views, rather than just look at SQL code, to determine index(es) usage and missing index(es).

How about something like below?  Since it involves renaming the table, naturally you may need to adjust the implementation details to avoid/lessen glitches in your specific environment:

1) Create...

The biggest performance factor overall is properly clustering the tables.  Presumably you always query by client (if you have clients that see multiple clients, you might need another...

I wouldn't worry about a one-time occurrence (although you should review whether or not you are on the latest SP/patch for that SQL instance).

If it happens again, then...

Oops, sorry, I should have included that part.

SELECT *
FROM sys.indexes
WHERE index_id = 47
AND object_id = OBJECT_ID('_ACTIVITY_GEN')

Great, though, that it was index #47, that...

Identity the rows can be extremely difficult.

First check to see if it's a nonclus or clus index/heap.  If the errors are in a nonclus index, simply drop and...