One of my teammate was doing mistake. And he was getting error as 

"The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value"

-->The appointmentdatetime in the table was datetime. And in dd./mm/yyyy format. He was converting in the query rest of the things to 103(mm/dd/yyyy) format except the appointmentdatetime. Below are the suggestion how the error is rectified.

Error 1:

àIf you want to do with 101[mm/dd/yyyy]. 

-->Appointmentdatetime was in default [dd/mm/yyyy]

The highlighted part you were not converting it to 101. So you have to convert into 101.

select route_id, appointment_datetime ,facility_name,Registration_number,case when Route_type_id = 1then 'PICK' else 'DROP' end as Route_type     

,vendor_name, case  when arrival_time is null then convert(varchar(300),appointment_datetime,101) elseconvert(varchar(100),appointment_datetime,101)+ ' ' +arrival_time  end as Arrival_Time,     

case

when CONVERT(varchar(100),appointment_datetime,101) + ' ' +CONVERT(varchar(100),appointment_datetime,108)

< convert(varchar(100),appointment_datetime,101)+ ' ' +arrival_time

then 'LATE'

else 'ON TIME'

end as STATUS 

     

from tbl_route_header RH with (NOLOCK), tbl_facility TF with (NOLOCK),tbl_vehicle_master VM with(NOLOCK), tbl_user_master Um with (NOLOCK)     

where  RH.hub_id = Tf.facility_id     

and RH.vehicle_id = VM.vehicle_id     

and RH.vendor_id = Um.userid     

and RH.inactive = 0    

and RH.hub_id =20 

Error 2:

àIf you want to do with 103[dd/mm/yyyy]. As appointmentdate time is already in dd/mm/yyyy

Format. So all you need to do is convert the rest of the things to 103 format.

select route_id, appointment_datetime ,facility_name,Registration_number,case when Route_type_id = 1then 'PICK' else 'DROP' end as Route_type     

,vendor_name, case  when arrival_time is null then appointment_datetime elseconvert(varchar(100),appointment_datetime,103)+ ' ' +arrival_time  end as Arrival_Time,     

case

when CONVERT(varchar(100),appointment_datetime,103) + ' ' +CONVERT(varchar(100),appointment_datetime,108)

< convert(varchar(100),appointment_datetime,103)+ ' ' +arrival_time

then 'LATE'

else 'ON TIME'

end as STATUS 

     

from tbl_route_header RH with (NOLOCK), tbl_facility TF with (NOLOCK),tbl_vehicle_master VM with(NOLOCK), tbl_user_master Um with (NOLOCK)     

where  RH.hub_id = Tf.facility_id     

and RH.vehicle_id = VM.vehicle_id     

and RH.vendor_id = Um.userid     

and RH.inactive = 0    

and RH.hub_id =20

Happy Coding :) Njoy