x

How can I convert all datetime columns in a database to datetime2

Help

We've recently upgraded to SQL Server 2014 and I need to convert all of the datetime columns to datetime2 in a specific database.

Thanks Tim

more ▼

asked Feb 09, 2016 at 10:33 AM in Default

avatar image

TimFolkes
50 1 1 4

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.

Feb 13, 2016 at 12:54 PM Grant Fritchey ♦♦

Just be aware that there are things you can do with datetime that you can't do with datetime2. So you could end up breaking existing code. For example @my_datetime + 1 adds a day. @my_datetime2 + 1 fails.

Feb 15, 2016 at 06:59 PM GPO
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Googling the subject I came across this script in stack overflow but I don't know enough about the system tables to say whether it will work or not

 DECLARE @SQL AS NVARCHAR(4000)
 DECLARE @table_name AS NVARCHAR(255)
 DECLARE @column_name AS NVARCHAR(255)
 DECLARE @isnullable AS BIT
 
 DECLARE CUR CURSOR FAST_FORWARD FOR
     SELECT c.table_name, 
            c.column_name, 
            CASE WHEN c.is_nullable = 'YES' THEN 1 ELSE 0 END AS is_nullable
       FROM INFORMATION_SCHEMA.COLUMNS c 
      WHERE c.data_type = 'datetime'
        AND c.table_catalog = 'your_database'
        AND c.table_schema = 'your_schema'
     -- AND c.table_name = 'your_table'
 
 OPEN CUR
 FETCH NEXT FROM CUR INTO @table_name, @column_name, @isnullable
 WHILE @@FETCH_STATUS = 0
 BEGIN
     SELECT @SQL = 'ALTER TABLE ' + @table_name + ' ALTER COLUMN ' + @column_name + ' datetime2' + (CASE WHEN @isnullable = 1 THEN '' ELSE ' NOT' END) + ' NULL;'
     EXEC sp_executesql @SQL
     FETCH NEXT FROM CUR INTO @table_name, @column_name, @isnullable
 END
 
 CLOSE CUR;
 DEALLOCATE CUR;
more ▼

answered Feb 09, 2016 at 12:02 PM

avatar image

TimFolkes
50 1 1 4

(comments are locked)
10|1200 characters needed characters left

do you have any Referential Integrity in force in this database? you'll need to inspect the schema very closely to get an idea of what can and cant be changed without extra work being required

more ▼

answered Feb 09, 2016 at 11:34 AM

avatar image

perrywhittle
722 1 4

Yes I have referential integrity in force. The database in question has 100+ tables pretty all of which have clustered / non-clustered indexes with foreign key relationships between them.

Feb 09, 2016 at 11:57 AM TimFolkes

Any of the datetime columns part of these keys?

Feb 09, 2016 at 02:24 PM perrywhittle
(comments are locked)
10|1200 characters needed characters left

Here is another way to do this without using that cursor. You can leverage dynamic sql and the system tables to build a whole series of statements. I also avoided the INFORMATION_SCHEMA views as they don't always have the information you desire. I generally just avoid them to save myself the headaches. Here is a good article about those views. http://sqlblog.com/blogs/aaron_bertrand/archive/2011/11/03/the-case-against-information-schema-views.aspx

     DECLARE @SQL AS NVARCHAR(MAX) = ''
     
 SELECT @SQL = @SQL 
        + 'ALTER TABLE ' 
        + t.name 
        + ' ALTER COLUMN ' 
        + c.name 
        + ' datetime2 ' 
        + ( CASE WHEN c.is_nullable = 1 THEN '' ELSE 'NOT' END ) 
        + ' NULL;' 
   FROM sys.tables t 
        join sys.columns c on t.object_id = c.object_id 
        join sys.types st on st.user_type_id = c.user_type_id 
  WHERE st.name = 'datetime'
     
 SELECT @SQL 
 --EXEC sp_executesql @SQL --uncomment this line when you are certain the dynamic sql is correct.

more ▼

answered Feb 15, 2016 at 08:48 PM

avatar image

seanlange
997 1 4 6

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x87
x77

asked: Feb 09, 2016 at 10:33 AM

Seen: 804 times

Last Updated: Feb 16, 2016 at 01:09 PM

Copyright 2017 Redgate Software. Privacy Policy