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 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply