How to cast a Varchar column to DateTime

Ehtesham Siddiqui · #1 (**permalink**) 01-21-12, 06:55

Hi,
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.

Code:

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 + '%'	

Union 

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

Please guide how can i cast it correctly

Wim · #2 (**permalink**) 01-21-12, 07:35

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)

Here you can find what format parameter you have to use, depending on the format of the date/time in your VARCHAR column.

It's not clear to me what the problem is. Try to get the first part of your query good without the UNION and the second part. Only then add the second part and the UNION.

When do you get that error? With the first part, or only after you added the second part and the UNION?

Internet Services

Web Development

Digital Marketing

Consumer Tech