Global change of datetime datatype 
Author Message
 Global change of datetime datatype
I need to update the whole database to change
'datetime' to 'smalldatetime' database in all user
tables.

What is the best and fastest way of doing that?

Thank you in advance

Yulia



Mon, 20 Jan 2003 03:00:00 GMT
 Global change of datetime datatype

Hi Yulia

Quote:
> I need to update the whole database to change
> 'datetime' to 'smalldatetime' database in all user
> tables.

> What is the best and fastest way of doing that?

up to version 11.9.x there is no way to do that, because you can't
change the type of a column. you have to copy and recreate all tables.

in version 12.0 you can write a procedure to read the names of all
usertables from sysobjects. for each you can read the columns from
syscolumns. then you can create an sql statement to change the
datatype, execute that with the new execute immediate feature of
version 12.

hth
--
Bernd Dulfer
Sybase Professional Services Germany

Sent via Deja.com http://www.deja.com/
Before you buy.



Mon, 20 Jan 2003 03:00:00 GMT
 Global change of datetime datatype

Quote:

> Hi Yulia

> > I need to update the whole database to change
> > 'datetime' to 'smalldatetime' database in all user
> > tables.

> > What is the best and fastest way of doing that?

>  in version 12.0 you can write a procedure to read the names of all
> usertables from sysobjects. for each you can read the columns from
> syscolumns. then you can create an sql statement to change the
> datatype, execute that with the new execute immediate feature of
> version 12.

In ASE 12.0, you can use the ALTER TABLE MODIFY command
to change the datatype of existing columns. In this case, you would
do:

    alter table t1 modify datetime_col smalldatetime

Hope this works for you.

-- Aditya.



Sat, 01 Feb 2003 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Changing varchar to datetime datatype in SQL Server 7.0 Trigger

2. How can I change the default value of the datatype datetime

3. Transformation of an int datatype to datetime datatype

4. Converting datatype varchar to datatype datetime in SQL trigger

5. STORING DATETIME VALUES IN DTS GLOBAL VARIABLES

6. Datetime global variables in DTS

7. Global Constants (Creating a Boolean DataType)

8. wants to include seconds in datetime datatype

9. DateTime datatype in SQL Server 7 ???

10. Auto update datetime datatype

11. datetime datatype question

12. dateTime datatype - Two quick questions


 
Powered by phpBB® Forum Software