How would I be able to achieve the results I'm after ? This has had me stumped for quite a while now

Im matching up the 2 begtime columns, but within the matches found are 30 minute periods. (up to 48)  I only need 2 of these so I figured if I...

apologies, here's the complete sql code

select Trade.tradedate AS TradeDate,
Trade.status,
contract.contract AS ContractID,
fee.feemode,
position.counterparty,
position.positiontype,
Trade.cstcontractstart AS ContractStartDate,
Trade.cstcontractend as ContractEndDate,
contract.contracttype,
CONVERT(int,Trade.trade) AS...

I've actually had a rethink about this and decided to use a stored procedure activated through a job every 5 minutes, I don't need 'real time' updates for the particular...

position.loadshape is varchar(64) and its nullable

how do i get the actual plan ?

I wrote the query manually, 2 execution plans attached

I ended up putting the code inside a table valued function then using (select count from functionname) which has done the trick

DOH ! im an idiot, Id created a temp variable inside the function and trimmed any leading or trailing spaces from it, but id forgot to set the variables length,...

the only conditions that ever get trapped are these

WHEN @Tmp = '0' THEN CONVERT (time, '00:00:01.0000000')
WHEN @Tmp = '2400' THEN CONVERT (time, '23:59:59.0000000')

Ive changed the varchar size

ALTER FUNCTION [dbo].[ConvertStringToTime]
(
    @T VarChar(4)
)

but im still getting null returned

it makes no sense why this code works perfectly fine if its inline, but it wont work inside a function

there are no colons in the starttime the values will always be like this

1300
900
1100
2030
0
700

etc,etc

using this

TRY_PARSE(START_DATE AS datetime using 'en-GB') AS START_DATE,

fixed the problem