change of datatype - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

change of datatype

Author

Topic

asm
Posting Yak Master

140 Posts

Posted - 2009-08-22 : 08:53:45

How to change the "datetime" datatype to "date"
in all table of the database.

how to generate procedure to do this?

asm

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-22 : 09:01:42

are u using sqlserver 2005 or sql server 2008

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-22 : 09:02:19

in sql 2008,

alter table table_name alter column column_name DATE

In sql 2005 server, there is no DATE datatype

asm
Posting Yak Master

140 Posts

Posted - 2009-08-22 : 09:10:52

using sql 2008
but want to change in all table in single query/procedure

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-22 : 12:17:25

Set the output to text in SSMS and execute the query below. Then copy/paste the results back in and execute that


select	'ALTER TABLE [' + t.name + '] ALTER COLUMN [' + c.name + '] date;
GO'
from	sys.columns c
join	sys.tables t
on	t.object_id = c.object_id
where	system_type_id = 61

use system_type_id = 58 for smalldatetime

asm
Posting Yak Master

140 Posts

Posted - 2009-08-23 : 07:48:48

what system_type_id of datetime

asm
Posting Yak Master

140 Posts

Posted - 2009-08-23 : 07:50:35

and system_type_id of date

asm
Posting Yak Master

140 Posts

Posted - 2009-08-23 : 07:50:39