Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Unanswered: How to cast a Varchar column to DateTime

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •