Conversion error while converting date and / or time from character string Error

Question

Select CONVERT(Date, '13-5-2012') 

When I run the above T-SQL statement in Management Studio, I get the following error:

"Conversion error while converting date and / or time from character string"

Is it possible that I can apply this value to a valid date type? I have such values ​​in the nvarchar (255) column, whose dataType I want to change to the Date type in the SQL Server table, but I hit this error, and I would like to first convert in the Update statement in the table.

Answer 1

Indicate what date format you use:

 Select CONVERT(Date, '13-5-2012', 105) 

105 stands for Italian Century Date Format (dd-mm-yyyy).

Link: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 2

In general, I suspect that there is usually data that cannot be converted to a column, and will use the case statement, which checks it is converted first:

 SELECT CASE WHEN ISDATE(mycolumn)=1 THEN CONVERT(Date, mycolumn, [style]) END FROM mytable 

I believe Convert relies on setting the SQL Server date format. Check your dateformat setting with DBCC USEROPTIONS .

I suspect you will set the dateformat to dmy , this will understand:

 SET DATEFORMAT dmy GO 

If even then this does not work, you cannot find a style that matches your data, and if your data is in a format with a sequence, then you need to build it for manual string manipulation (do not do this if you can help).

Answer 3

Try it....

 Select CONVERT(Date,'5-13-2012') 

Use the format "mm-dd-yyyy".

Answer 4

CONVERT assumes that the source data can represent a date. One bad data item may cause the conversion error indicated here without indicating a problem.

Using ISDATE helped me get around bad data elements.

SELECT CONVERT (DATE, CONVERT (CHAR (8), FieldName)) FROM DBName WHERE ISDATE (field name) <> 0

Answer 5

You need to specify the date format during conversion, this will fix the error.

select convert (date, '13 -5-2012 ', 103)