David David - 3 months ago 137
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

DateTime
, obviously. However, here is my problem:

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

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
datetime2
to
datetime
, WITHOUT losing the data I have saved in the table?

Answer

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.

Comments