Data Migration in sql 2005

Question

In my Live sql database ,I have to change the date value from (date, month ,year) to date. Now there is 100 records with the date as date,month ,year(3 Fields).iF I change directly to date Field all the datas in the 3 field of those 100 records will automaticly change to a default date and original dates will disappear.What should i do to migrate all my datas safely

Answer 1

Add the new column (ALTER TABLE), populate it, and drop the old ones when you are ready to do so.

Answer 2

Make a backup. Add the new column, allowing the value to be null. Update the new column using a date constructed from the existing columns for month/day/year. Change the new date column to disallow nulls (if appropriate). Update your code to use the new column instead of the old column, then remove the old columns. If it is not possible to update all of your code, you may need to add triggers to keep the old and new columns in sync.