T-SQL: Convert datatime2 to datetime for all columns of type datetime2

Question

I've got a database full of datetime2 columns than needs to be moved to a SQL 2005 database. So, I need to convert all these datetime2(7) columns to datetime.

How can I go about doing this?

Right now I've managed to select the table name and column name for all columns with the datetime2 datatype like this:

SELECT t.name, c.name, i.DATA_TYPE
FROM sys.tables AS t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN information_schema.columns i ON i.TABLE_NAME = t.name AND i.COLUMN_NAME = c.name
WHERE i.data_type = 'datetime2'

I just don't know how to do the rest.

Answer 1

... then you iterate over your results with the CURSOR and dynamically run the DDL like:

ALTER TABLE myTable ALTER COLUMN myColumn datetime [NOT] NULL

so that you get something similar to this (not tested):

Edit: added null-ability check as well:

DECLARE @SQL AS NVARCHAR(1024)
DECLARE @TBL AS NVARCHAR(255)
DECLARE @COL AS NVARCHAR(255)
DECLARE @NUL AS BIT
DECLARE CUR CURSOR FAST_FORWARD FOR
    SELECT  t.name, c.name, c.is_nullable
    FROM    sys.tables AS t
    JOIN    sys.columns c ON t.object_id = c.object_id
    JOIN    information_schema.columns i ON i.TABLE_NAME = t.name AND i.COLUMN_NAME = c.name
    WHERE   i.data_type = 'datetime2'
    ORDER BY t.name, c.name

OPEN CUR
FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = 'ALTER TABLE ' + @TBL + ' ALTER COLUMN ' + @COL + ' datetime' + (CASE WHEN @NUL=1 THEN '' ELSE ' NOT' END) + ' NULL;'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM CUR INTO @TBL, @COL, @NUL
END

CLOSE CUR;
DEALLOCATE CUR;

Answer 2

I know this thread is old but I'm doing the same thing today and just wanted to offer up my technique. Whenever I need to do a lot of DDL statements I create one TSQL that generates the TSQL that's needed and then just copy the results into the Query window and run it. You don't need to write all the cursor code like @van suggestion (although that works fine).

So, for your situation, just run the sql statement:

select 'ALTER TABLE ' + table_name + ' ALTER COLUMN ' + column_name + ' datetime [NOT] NULL' 
from INFORMATION_SCHEMA.columns 
where data_type = 'datetime2(7)'.

Then, copy the results into a new query window and run it. Sometimes you need to add "GO" statements on their own line between commands. If so, add char(13) + 'GO' into your output string.

Also, make sure to run the query in SQL Mgmt Studio with the "Results to Text" option instead of the "Results to Grid" option.