Converting data types

Having a slight problem. For whatever reason I had a column setup for nvchar but the only data i have in there is dates (MM/DD/YY) and a whole bunch of NULLs. I wanted to change the datatype to datetime but it gave me an error. I thought it was due to all the NULLs, so I removed all the nulls. It still doesn't work. I get the error : Conversion failed when converting datetime from character string.

I don't belive its because I have the data in mm/dd/yy format. I created another table and populated it with mm/dd/yy data as nvchar (50) and then converted to datetime and it worked fine.

any ideas?

Obviously, as you know, you get this error because the conversion could not convert some of the data (as it was not in the correct format). First of all, you can use IsDate function to see which data is giving you problem. Other than that, try this post. Just as a precaution, backup the table or database before you try anything.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1785290&SiteID=1

|||

Rather than trying to update the data type, try creating an additional field with a DateTime type. You can then copy all the dates that aren't null into this column and then drop the original column (and rename the new one if necessary).

|||

I would look through the data. When dates are stored as varchar, and especially if there isn't any validation when it is inserted into the database, you'll often see values like 01/01/3007 or 06/32/2007 or some other way it was entered that caused ambiguity with SQL Server. I've had the same thing happen in the past.

|||

Hi,

Review the Globalization configuration in the web.config, put en-US to work fine in the culture and uiculture attributes, by default is en-US, may be you change it.

greetings.