January 21, 2012 at 4:59 am
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.
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
January 21, 2012 at 8:22 am
It appears you may be attempting to convert a string that is not in a proper date/time format. For example your string may be '15/35/2012 27:72:66.123'; there's no Month > 12, Day of Month > 31, Hour > 24, Minute > 60 or Second > 60.
Create an additional column that will leave that field as a varchar, and in the conversion (casting) of that field, use it in a case statement along with the ISDATE() function to determine if the string that you're passing in is a valid date and then and only then cast the string as a datetime, otherwise (the else part of the case statement) provide some default date value that you can easily spot or sort on to identify those date/time strings that are out of range as your error is telling you.
This should help you identify the data that needs to be cleaned or possibly discarded instead of it blowing up your code.
January 21, 2012 at 2:44 pm
Another reason might be the varchar value is in a different date format than the DATEFORMAT setting of SQL Server for the code block/session the query is used in.
Example: the date '01/15/2012' is valid for a mdy (MonthDayYear) setting but it'll fail id DATEFORMAT is set to dmy (DayMonthYear) since it'll read 15 as a month.
Also, I'm wondering about the error message you describe and the code you posted: When trying to cast an invalid value to a TIME data type, the error would be "Conversion failed when converting date and/or time from character string." (Msg 241). The error message you mentioned is raised if you try to cast as DATETIME (Msg 242). Please clarify.
January 21, 2012 at 3:16 pm
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply