How to Change all tables and columns in DateTime

Question

I have to change all the columns in a database in all the tables (SQL Server) that are Date and convert them to DateTime.

How do I accomplish that? Is it a command?

What have you tried so far? Have you considered the ALTER TABLE statement? — Ian, Dec 14 '18 at 13:53
Can I recommend that you *don't* convert them to `datetime`, https://stackoverflow.com/questions/1334143/datetime2-vs-datetime-in-sql-server. Even MSDN recommends using `datetime2`. — Richardissimo, Dec 14 '18 at 14:03

Ross Bush · Answer 1 · 2018-12-14T15:17:44.290

0

The command for altering a column, which must be called to change a datatype, is --> ALTER COLUMN. The ALTER COLUMN command can only be called from within the context of an ALTER TABLE command. You are lucky because the datatype you want to convert to is conversion compatible and only widens the column. If you attempt to convert to a incompatible datatype or a datatype that requires less storage then you could not use the simple command below.

ALTER TABLE YourTableName
ALTER COLUMN YouDateField DATETIME

edited Dec 14 '18 at 15:17

answered Dec 14 '18 at 13:55

Ross Bush

14,648
2
32
55

Please add some context to your answer do the OP can better understand it. – Flame_Phoenix Dec 14 '18 at 14:58
Updated with context :/ – Ross Bush Dec 14 '18 at 15:17

score 0 · Answer 2 · answered Dec 14 '18 at 13:59

I think this can help you :

Execute the script you will see the update list in the messages tab.

DECLARE @SQLString VARCHAR(MAX)=''

SELECT @SQLString  = @SQLString +CHAR(13)+'ALTER TABLE '+T.TABLE_SCHEMA+'.'+T.TABLE_NAME+' ALTER COLUMN '+C.COLUMN_NAME+' DATETIME'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_SCHEMA=C.TABLE_SCHEMA 
                                       AND T.TABLE_NAME=C.TABLE_NAME
WHERE C.DATA_TYPE='date'
AND T.TABLE_TYPE='BASE TABLE'

PRINT(@SQLString)

score 0 · Answer 3 · answered Dec 14 '18 at 14:04

you can use this query to get collection of alter queries for your columns which are date type.

SELECT 
  CONCAT('alter table ', TABLE_SCHEMA, '.', TABLE_NAME,  ' ALTER COLUMN ', COLUMN_NAME, ' DATETIME;') 
FROM INFORMATION_SCHEMA.COLUMNS
Where DATA_TYPE = 'date' and TABLE_SCHEMA not in ('information_schema', 'sys');

then by executing generated results your table columns will be converted to DateTime

Zhorov · Answer 4 · 2018-12-14T17:12:19.287

Generate dynamic statement using sys.columns and sys.types and execute generated statement. This statement also checks for nullability of each column. ALTER TABLE ... ALTER COLUMN is not always a working solution. In some cases you'll probably need to drop the table and recreate it, or add new column, then copy the values for old column to the new one, and then drop the original column.

When you alter a column, consider at least the following:

the previous data type must be implicitly convertible to the new data type
new type cannot be timestamp.
ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable.

T-SQL:

DECLARE 
    @stm nvarchar(max),
    @err int

SELECT @stm = (
    SELECT CONCAT(
        'ALTER TABLE ', OBJECT_NAME(c.OBJECT_ID), 
        ' ALTER COLUMN ',
        c.name,
        ' datetime',
        CASE 
            WHEN c.is_nullable = 1 THEN ' NULL '
            ELSE ' NOT NULL '
        END,
        '; '
    )
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id = t.user_type_id
    WHERE t.name = 'date'
    ORDER BY c.OBJECT_ID
    FOR XML PATH('')
)

PRINT @stm
EXEC @err = sp_executesql @stm
IF @err = 0 
    PRINT 'OK'
ELSE 
    PRINT 'Error'

How to Change all tables and columns in DateTime

4 Answers4