This might make more sense. Formatting is never my thing.
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
select '1911-03-02T14:00:00.000', '1300' union all
select '1911-03-02T14:00:00.000', '0950'
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.
;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
select *, DATEDIFF(n, check_in, AppointmentTime) from cte
I think that is what you are looking for, or at least it is reasonably close.
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)