Menu
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?
Thank you,
Daniel
868
The main difference is the way of data storage: while in Datetime type, the date comes first and then time, in Datetime2, 3 bytes, in the end, represents date part!
Microsoft recommends using DateTime2 instead of DateTime as it is more portable and provides more seconds precision. Also, DateTime2 has a larger date range and optional user-defined seconds precision with higher accuracy.
1 Answer. Show activity on this post. To provide an answer, in short Neither Datetime nor Datetime2 encodes timezone information, only the raw date/time data specified.
However I can't just switch the type of the column from datetime to datetime
Sure you can, use ALTER TABLE TableNAme ALTER column ColumnNAme datetime2
example
USE tempdb GO CREATE TABLE Test(SomeDate DATETIME) INSERT Test values (GETDATE()) SELECT * FROM Test GO ALTER TABLE Test ALTER column SomeDate datetime2 GO INSERT Test values ('16000101') SELECT * FROM Test GO If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
