Along these lines in SQL Server 2012 or later.So you mean its not compatible with earlier versions of SQL?
SELECT V.Schedule_VisitStartTime,V.Schedule_VisitEndTime,replace(convert(varchar(5),V.Schedule_VisitStartTime,108)+'-'+convert(varchar(5),V.Schedule_VisitEndTime,108),':','')
FROM V_TestHHASchedule AS V
11/10/2018 7:00:00 AM 11/10/2018 7:00:00 PM 11/10-11/10SELECT V.*
FROM V_TestHHASchedule AS V
WHERE not Exists (SELECT NULL FROM [VisitReport] R
--WHERE charindex(V.patients_firstname,R.pat ientname) > 0
where RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ) = RTRIM( LEFT( v.patients_lastname, CHARINDEX( ' (', v.patients_lastname + ' (' ) ) ) + ' ' + RTRIM( LEFT( v.patients_firstname, CHARINDEX( ' (', v.patients_firstname + ' (' ) ) )
and RTRIM( LEFT( r.aideName, CHARINDEX( ' (', r.aideName + ' (' ) ) ) = RTRIM( LEFT( v.caregivers_lastname, CHARINDEX( ' (', v.caregivers_lastname + ' (' ) ) ) + ' ' + RTRIM( LEFT( v.caregivers_firstname, CHARINDEX( ' (', v.caregivers_firstname + ' (' ) ) )
and datediff(d,V.Schedule_VisitDate,R.vi sitdate) = 0
and replace(convert(varchar(5),V.Schedul e_VisitSta rtTime,108 )+'-'+conv ert(varcha r(5),V.Sch edule_Visi tEndTime,1 08),':','' ) = R.scheduled
alter table V_TestHHASchedule alter column schedule_visitdate datetime
alter table V_TestHHASchedule alter column schedule_visitstarttime datetime
alter table V_TestHHASchedule alter column schedule_visitendtime datetime
And everthing worked as expected....set dateformat MDY
select * into V_TestHHASchedule
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=c:\mrwtest\ee\V_TestHHASchedule.xls;HDR=yes', 'SELECT * FROM [V_TestHHASchedule$]') as a
go
select * from INFORMATION_SCHEMA.columns where table_name = 'V_TestHHASchedule'
go
alter table V_TestHHASchedule alter column schedule_visitdate datetime
alter table V_TestHHASchedule alter column schedule_visitstarttime datetime
alter table V_TestHHASchedule alter column schedule_visitendtime datetime
go
By way of comparison, look at what happens when convert works with a string vs a datetime :
select replace(convert(varchar(5),'2018-12-12 07:08:09.123',108)+'-'+convert(varchar(5),'2018-12-12 08:09:10.123',108),':','')
select replace(convert(varchar(5),cast('2018-12-12 07:08:09.123' as datetime),108)+'-'+convert(varchar(5),cast('2018-12-12 08:09:10.123' as datetime),108),':','')
In the string date, the STYLE CODE of 108 basically tells Convert how to read the string. When it is a datetime, the style code of 108 tells converrt how to format the datetime.
i.e. which database syntax do you want for the comparison?
what date/time is "1459-2304" in yyyy-mm-dd hh:mm format ? (i.e. how is this compared to a normal date/time?)