Search code examples
sqlsql-servert-sqldatenvarchar

Tsql nvarchar date update

I have a table(tabl) with column(Col) that is nvarchar(10) data type.

I am not suppose to change the data type or data length. The column contains date in this format yyyy-mm-dd (2012-04-24) I need to update that column to mm/dd/yyyy(04/24/2012).

Solution

  • That's an awful set of constraints, but what you're asking is possible:

    UPDATE tabl SET col = CONVERT(nvarchar(10), CAST(col AS datetime), 101)

    The 101 at the end is the appropriate date style grabbed from this page: http://msdn.microsoft.com/en-us/library/ms187928.aspx

    The result is, though, that you'll have dates that are difficult to order and are format ambiguous. Storing as a datetime is the respectable thing to do.