Conversion failed when 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 i get the following error:

"Conversion failed when converting date and/or time from character string"

Is there away i can cast that value to a valid Date type successfully? I have such values in a nvarchar(255) column whose dataType i want to change to Date type in an SQL Server table but i have hit that error and i would like to first do a conversion in an Update statement on the table.

Answer 1

Specify what date format you are using:

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

105 means Italian date format with century (dd-mm-yyyy).

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

Answer 2

In general, I'd suspect usually there is data which can't be converted in a column, and would use a case statement checking it's convertable first:

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

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

I suspect if you set the dateformat to dmy it'll understand:

SET DATEFORMAT dmy
GO

If even then it doesn't work, you can't find a style that matches your data, and if your data is in a consistant format, it's down to manual string manipulation to build it (don't do this if you can help it).