No you are not asking the impossible but you did not post this like I suggested (ddl and sample data). In order to make this work we need something that we can copy and paste into SSMS so we can work on your problem instead of spending time setting up the problem. I know you are new around here so I did this for you as an example.

Here is your data is an easily consumable format.

create table #CheckIn

(

check_in datetime,

appt_time char(4)

)

insert #CheckIn

select '1911-03-02T14:00:00.000', '1300' union all

select '1911-03-02T14:00:00.000', '0950'

select *

from #CheckIn

The advantage is two fold. The volunteers that are going to help with your issue don't have to write this first. Instead they can spend their time working on the issue at hand with little setup effort. Secondly, there is no doubt about datatype and such.

OK so what so I demonstrated how you should post questions in the future. What you really care about is some help with your solution.

Try this.

;with cte as

(

select check_in, appt_time, dateadd(n, CAST(right(appt_time, 2) as int), dateadd(hh, cast(left(appt_time, 2) as INT), dateadd(dd, datediff(dd, 0, check_in), 0))) as AppointmentTime

from #CheckIn

)

select *, DATEDIFF(n, check_in, AppointmentTime) from cte

I think that is what you are looking for, or at least it is reasonably close.