When attempting to retreive and use data from a table in the below script I hit the following error select CONVERT(datetime, users.MemberJoinDate, 121) as MemberDate from users Error Msg 241, Level 16, State 1, Line 1 Conversion failed when converting datetime from character string. Although if I take the data from within one of the users.MemberJoinDate cells and run the following it works? select CONVERT(datetime, ‘Feb 9 2011 5:39PM’, 121) as MemberDate from users Any ideas why it is failing to pull the data?
This is where DATETIME should never be stored in other format. But If you still want to store it in other format then store in universal formats like yyyymmdd hh:mm:ss.mmm, yyyy-mm-ddThh:mm:ss.mmm OR yyyymmdd (if you do not need time part to be stored). I would feel that you are lucky that an error is generated. Suppose If the dates are never greater than 12. You could have been reporting MONTHS as DAYS and DAYS as MONTHS. In your case, you can use ISDATE() function to check which date is not in the correct format. Something like SELECT CASE WHEN ISDATE(users.MemberJoinDate) = 1 THEN 'YES' ELSE 'NO' END AS IsaValidDate, users.MemberJoinDate FROM users you should use SET LANGUAGE and SET DATEFORMAT in addition to ISDATE, according to your environment. See examples at [BOL] Moreover, I would use only CONVERT(DATETIME, users.MemberJoinDate) with no format, since SELECT CONVERT(datetime, '20120101T23:59:57.997', 121) will throw an out of range exception but it is a valid date. There is no need to specify date format unless and until you want to show it in a desired format (Formatting should be done at the application level). If you still want to use the format use 126 instead of 121. :
Our way of handling this is to default the date in the MemberJoinDate column. That way all values in the column are valid dates. The data in this column is never user input and never NULL.
To implement this, you can create a new column, copy all of the valid dates to the column and for the remaining columns do a manual input.