How can i convert this data in sql server

Question

my date column type NVARCHAR(50) example date 02 September 2010 Friday i want to CONVERT DATETIME for can make ORDER BY ..

i use ORDER BY CONVERT(DATETIME,M.DeletedDate,102) DESC and i tried 103,104,105......

error message = Conversion failed when converting date and/or time from character string.

how can i convert that value to DATETIME or SMALLDATETIME please help me !

Answer 1

It doesn't like the day of the week on the end. Here's one way to strip that off:

DECLARE @Dt nvarchar(50)
SET @Dt = '02 September 2010 Friday'
SELECT Convert(datetime, Substring(@DT, 1, Len(@Dt) - CharIndex(' ', Reverse(@Dt))), 106)

However, you probably already know that dates stored as strings are very inefficient. If there's any way you can change the column to be a real datetime column, or to add another datetime column where you can store the real datetime, you should do it. You will get bad performance if there are ever any conditions on the column because it will be forced to do string conversion for each row in the table. Ouch!

Answer 2

The list of types is documented at CAST and CONVERT (Transact-SQL). The closest to your style is 106 (d mon yyy) but that would understand 2 Sep 2010 and actually it would also understand 2 September 2010. But no style would understand 2 September 2010 Friday. That style is highly unusual, computers don't need to be reminded what day of the week a date is, they know...

You're going to have to change your data format to a valid data format. In fact, you should use store DATETIME as DATETIME to begin with and don't do costly converts. IF the data comes 'as is' from a foreign source, then you must convert it as appropriately before storing it into the table, this is what the whole ETL process is all about.

Answer 3

thanks for answers. I added a new column of type smalldatetime and trigger for set that column. I will use this column to sort and time to show the other column.