David David - 2 months ago 20
Vb.net Question

Converting datetime2 to datetime in SQL Server

I have a vb.net project that I converted from Access to SQL Server.

In Access, all of my date/time fields were saved as

, obviously. However, here is my problem:

When I migrated the database over to SQL Server 2008, it is now saving them as
columns, rather than
. This is causing problems in Crystal Reports, as it converts these to strings, not

I have hundreds of records in the table, so I can't delete it and re-design it, so is there any other way I can change the columns from
, WITHOUT losing the data I have saved in the table?


The fix to this was to use queries in SQL Server.

  • ALTER TABLE table_name ADD new_column datetime
  • SET new_column = old_column
  • ALTER TABLE DROP old_column

This may also include the need to edit primary keys etc and relationships but these can just be added straight back in again. Finally, rename the new column with the old columns name, and move it into the correct location.