Someone asked how to change the data type from datetime to datetime2
Here is the question
I have a SQL Server 2005 database with a datetime column. There is already data in the table but now the customer needs dates before 1753. So I decided to migrate the database to a SQL Server 2008 to use the datetime2 type.
However I can’t just switch the type of the column from datetime to datetime2. Is there a way to do this conversion or do I have to reimport the data?
Someone else answered the following
Here is an answer that this person got from someone
You could add the new column.
UPDATE Table SET NewColumn = OldColumn
delete the old column
then rename the new column.
This of course is highly inefficient. Just imagine running that suggestion on a table with millions or billions of rows.
My approach would be to do this instead: ALTER TABLE TableName ALTER COLUMN ColumnName datetime2
Let’s take a closer look at the T-SQL needed for this
First create the following table and insert one row
USE tempdb GO CREATE TABLE Test(SomeDate DATETIME) INSERT Test values ('20110410') SELECT * FROM Test GO
Datetime accepts dates in the range from January 1, 1753, through December 31, 9999. If you try to insert a value before January 1, 1753, it will fail
Run the code below which will try to insert January 1, 1600, to see the error
INSERT Test values ('16000101')
_Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value._
Now we will change the column from datetime to datetime2.
The syntax looks like this
For our table the syntax will be the following: ALTER TABLE Test ALTER column SomeDate datetime2
Run the code below to make that happen
ALTER TABLE Test ALTER column SomeDate datetime2 GO
So now, if we try to insert January 1, 1600, it should succeeed
INSERT Test values ('16000101')
Now, you can look what is in the table
SELECT * FROM Test GO
Just be aware that if you are changing data types, make sure that what you change to can hold the current values. If you are changing from varchar to integer, make sure you only have values that can be converted to integers, the operation will fail if they can’t be converted to integers.
Learn the product and learn it well. Don’t overly depend on the wizards in SSMS, and if you use SSMS, hit the Script button to see what kind of T-SQL SSMS generates.
The wizards are nice but sometimes they get it wrong. Here is the code that the wizard generates to change the column
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO CREATE TABLE dbo.Tmp_Test ( SomeDate datetime2(7) NULL ) ON [PRIMARY] GO ALTER TABLE dbo.Tmp_Test SET (LOCK_ESCALATION = TABLE) GO IF EXISTS(SELECT * FROM dbo.Test) EXEC('INSERT INTO dbo.Tmp_Test (SomeDate) SELECT CONVERT(datetime2(7), SomeDate) FROM dbo.Test WITH (HOLDLOCK TABLOCKX)') GO DROP TABLE dbo.Test GO EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT' GO COMMIT
I definitely don’t want that either, that creates a whole new table..yikes
Spend some time in Books On Line, maybe every day at lunch open a random topic and read it for half and hour and run the code examples. If you commute, download the topic to your local device or hit the online version and study it. Another good way to learn is of course hitting the newsgroups where you will see top notch advice from SQL Server experts
The more you know, the better it will be for you and your employer, if suddenly you can prove that yes, we can do this and here is a better way I found, you will be rewarded sooner or later.