Unanswered: How to cast a Varchar column to DateTime
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value
My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID
Where D.SchoolID = @DojoID
and C.Days like '%' + @Days + '%'
Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID
Where D.SchoolID = @DojoID and @Date
Between E.EventStartDate and E.EventEndDate
You do a CAST to a TIME data type, but the other columns seem to be of DATETIME or DATE date type. Most likely the values in the VARCHAR are like
'2016-10-23 20:44:11', you cannot fit that into a TIME data type, the DATE part is lost, but it doesn't raise an error.
If there were errors in your data, like '25:84:71' (more hours than 24, more minutes than 60, more seconds than 60 ...) that would give another error message.
Change Cast(C.ClassTime as time) as StartDate
into Cast(C.ClassTime as datetime) as StartDate
or use CONVERT(datetime, C.ClassTime, 120)