title: SQL Server 2008: Store a date in nvarchar field
body: <p>I have a common field in a database table on SQL Server 2008.  The type on the field is nvarchar(255).  That field holds a number of different types of values coming from an application.</p>
  
  <p>I currently have need to turn values in the field from a format of "Jul 19 2011 12:00AM" for example to a format of "2011-07-19 00:00:00.000".</p>
  
  <p>I know I can get this back in a query format with the statement:</p>
  
  <pre><code>SELECT CONVERT(datetime, value, 121)
    FROM [Table]
    where ...
  </code></pre>
  
  <p>This gives me a display format that I am looking for, but when I try to update the table with the corrected format using the following query, it converts it back to the "Jul 19 2011 12:00AM" format.</p>
  
  <p>Update query:</p>
  
  <pre><code>Update [Table]
    set Value = CONVERT(datetime, t2.value, 121)
    from [Table] t1
    inner join
    (SELECT ID, CONVERT(datetime, value, 121) as value
    FROM [Table]) t2
    on t2.ID = t1.ID
  </code></pre>
  
  <p>Is there any way to force the destination nvarchar(255) field to maintain the "2011-07-19 00:00:00.000" format specified from the convert?</p>