Altering a table column from varchar to datetime
is pretty straight-forward in the SQL Server Management Studio (SSMS),
until you look at the tsql generated. For many operations SSMS will
generate a tsql script that will:
SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.
SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.
In this particular case we were modifying a varchar(10) to a datetime. All the data was in a valid format. The easiest method is to alter the column, another method to complete this is to:
- create a temporary table
- drop all the foreign keys
- copy the data to the temporary table
- create the new table with the correct data type
- copy the data to the new table
- drop the temporary table
- add the foreign keys back
SQLCricket comments that it is possible to change the options in SSMS to warn on table operations, i think this is only in sql 2K8.
SQLPuma comments that it is possible to change a varchar() to a datetime via tsql with an alter table alter column command.
In this particular case we were modifying a varchar(10) to a datetime. All the data was in a valid format. The easiest method is to alter the column, another method to complete this is to:
- rename the existing column (tmp_varchar etc)
- add a new column with the correct name
- update the new column (in batches if necessary)
- drop the original column (now with a tmp_name)
CREATE TABLE dbo.testing
(test_id bigint NOT NULL IDENTITY (1, 1) primary key,
some_dt varchar(10)
)
GO
insert into testing (some_dt) values ('2009-01-01')
insert into testing (some_dt) values ('2009-01-02')
insert into testing (some_dt) values ('2009-01-03')
insert into testing (some_dt) values ('2009-01-04')
go
EXECUTE sp_rename 'dbo.testing.some_dt',
'Tmp_some_dt', 'COLUMN'
GO
Alter Table testing
Add some_dt DateTime Default('1900-01-01') NOT NULL
GO
Update testing
Set some_dt = Convert(DateTime,Tmp_some_dt)
GO
Alter Table testing
DROP Column Tmp_some_dt
GO
select * from testing
go
--drop table testing
No comments:
Post a Comment