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.

Why are you joining three tables when all that information can be obtained from information_schema.columns? — Kibbee, Sep 18 '09 at 20:56
@van, @David. You may want to check column is_nullable in sys.columns to verify if NOT NULL is necessary in ALTER COLUMN — Lukasz Lysik, Sep 18 '09 at 20:58

van · Accepted Answer · 2009-09-18T21:07:44.670

6

... 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;

edited Sep 18 '09 at 21:07

answered Sep 18 '09 at 20:53

van

74,297
13
168
171

Sorry, I really don't know much SQL. Can expand on that? – David Murdoch Sep 18 '09 at 20:59
added code to the answer. basically you need to change the type of the column, right? you can do it using ALTER TABLE ... ALTER COLUMN ... syntax. With the help of cursor you can just execute for all columns you found in a kind-of loop. You might add check for column nullability as noted by Lucasz and add NOT NULL to the statement. – van Sep 18 '09 at 21:03
Thanks for your help! I think it might work. But I've also got another problem because many of these columns have constraints/triggers. I get an error stating that "the object 'DF_CreatedDate' is dependent on column 'CreatedDate'. – David Murdoch Sep 18 '09 at 21:21
in this case you might generate another dynamic SQL and execute it just before the ALTER COLUMN... : ALTER TABLE xxx NOCHECK CONSTRAINT DR_CreatedDate... or something similar. but for cases like this you might want to see what is the code for this constraint and if it is something like DEFAULT (GETDATE()), then you might first delete it, then change datatype, then add it back. – van Sep 18 '09 at 21:33

score 3 · Answer 2 · answered Nov 14 '17 at 09:34

Improved the above answer to cater for schemas

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

OPEN CUR
FETCH NEXT FROM CUR INTO @TBL, @COL, @SCH, @NUL
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @SQL = 'ALTER TABLE ['+@SCH+'].[' + @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,@SCH, @NUL
END

CLOSE CUR;
DEALLOCATE CUR;

Wish I'd seen this answer sooner, literally just wrote exactly the same SQL (except I used a local cursor). It's notable this answer also accounts for table and column names with spaces/special characters, while the accepted one doesn't — Erik A, Jan 02 '18 at 10:59

score 3 · Answer 3 · edited Jan 02 '18 at 11:06

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.

Just a final follow up, I'm converting all my datetime columns to datetime2(7) so here's the SQL I used: `select 'ALTER TABLE [' + t.table_name + '] ALTER COLUMN [' + column_name + '] datetime2(7) ' + (case when is_nullable = 'NO' then 'NOT' else '' end) + ' NULL' from INFORMATION_SCHEMA.columns c inner join information_schema.TABLES t on c.TABLE_NAME = t.table_name where data_type = 'datetime' and t.TABLE_TYPE = 'base table' order by t.table_name` — sisdog, Mar 31 '11 at 18:28

Cee McSharpface · Answer 4 · 2018-01-27T19:01:54.650

Needed to do this today for all user tables in a schema, and was not satisfied with any of the existing answers. Especially, some of my datetime columns had defaults, which actually nobody needed, but hindered ALTER TABLE commands. So I wrote a script that just drops those defaults, and then changes the columns. It preserves nullability, and can handle names containing spaces, hyphens etc. Caution, it does not recreate the defaults afterwards.

If you're in the same situation, you can use this stable and tested script, which also makes sure that there is no silent truncation of the nvarchar(max) variable used to compose the DDL statements:

DECLARE @sql AS nvarchar(max)=N''

--1. "ALTER TABLE [Tablename] DROP CONSTRAINT [DF__Tablename__Colname__Obfuscation]"
SELECT @sql=CAST('' AS nvarchar(MAX))+@sql 
  +N'ALTER TABLE ['+o.[name]+N'] DROP CONSTRAINT ['+co.[name]+']' 
FROM sysconstraints c 
INNER JOIN sysobjects o ON o.[id]=c.[id] 
INNER JOIN syscolumns col ON col.[id]=o.[id] AND col.colid=c.colid
INNER JOIN sysobjects co ON co.[id]=c.constid 
WHERE col.xtype=61 --datetime

EXEC sp_executesql @sql

--2. change type of all datetime columns
SELECT @sql=N''
SELECT @sql=CAST('' AS nvarchar(MAX))+@sql 
  +N'ALTER TABLE [' 
  +convert(nvarchar(max),t.name)
  +N'] ALTER COLUMN [' 
  +convert(nvarchar(max),c.name)
  +N'] datetime2 ' 
  +CASE WHEN c.is_nullable = 1 THEN N'' ELSE N'NOT' END
  +N' NULL;'+convert(nvarchar(max),char(13)+char(10))
FROM sys.tables t 
INNER JOIN sys.columns c ON t.object_id = c.object_id 
INNER JOIN sys.types st ON st.system_type_id = c.system_type_id
WHERE st.name=N'datetime'
AND t.xtype=N'U' --user tables only
ORDER BY t.[name]

EXEC sp_executesql @sql

It uses ancient syntax and schema tables, so it is working from SQL Server version 2008 (which was the first to support datetime2) thru 2016.

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

4 Answers4