Changing data type from DATE to CHAR(10) with a query

Question

I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query.

I want to alter all columns of that type in any table in this database.

How can I do this?

Answer 1

A piece of code that does what you want is:

declare @sql nvarchar(max), @crlf char(2) = CHAR(13)+CHAR(10);

select @sql = isnull(@sql, '') + 'alter table ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' alter column ' + c.COLUMN_NAME + ' CHAR(10)' + 
    + case when c.IS_NULLABLE = 'YES' then ' NULL;' else ';' end + @crlf
from INFORMATION_SCHEMA.COLUMNS c
where DATA_TYPE = 'date'

print @sql;
-- exec sp_executesql @sql --execute only after inspection!

But I would like to stress that you shouldn't normally do it on a system (except for dev/test purposes), because if it is Date information that you're using, you should be using the proper data type and avoid any other unnecessary cast.