asked
SQL CONVERT datatime failure
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?
it only takes one badly formed date for a date convert query to fail. Try and run the query on subsets of the data, then you may find the offending date.
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][1] 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. [1]:
http://msdn.microsoft.com/en-us/library/ms187347.aspx
+1. Very good answer. ISDATE is extremely useful, when importing from semi-unknown sources.
A quicker way of finding the data with the invalid date would be: SELECT * FROM users WHERE IsDate(MembersJoinDate) = 0
That doesn't cut it when importing data from manual sources, especially if those who entered the data are from different countries. What if you have 2011/05/01 and 2011/01/05? In sweden, a date is written either DD/MM-YYYY or YYYY-MM-DD. When swedes enter a date american style they tend to do it DD/MM/YY rather than MM/DD/YY. I'm not sure, but I think UK style is DD/MM/YY. So even when ISDATE(date)=1, the date can still be wrong.
@Magnus Ahlkvist +1. Yes you are right. But if the user would be the same, provided with the LANGUAGE settings, DATEFORMAT settings etc are the same, ISDATE() would work. That is where I have asked the OP to use SET LANGUAGE and SET DATEFORMAT with ISDATE() :) Infact it should be part of such scripts and settings should be according to the format expected. But my first priority would be to have datetime in universal formats, otherwise, it could become a bit messy.
@ThomasRushton +1. Yes, this definitely would be the quicker way.
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.
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.
