Alter Table Alter Column Syntax 
Author Message
 Alter Table Alter Column Syntax
Hi,

I have a table called "Invoice" with "InvoiceDate" field
defined to be "DataTime" data type with length 8 and Allow
Nulls true.

I need to change the Datatype of "InvoiceDate" field to
Varchar(20), Not NULL with Default "---".

Can this be done through SQL statements.

Regards,
Sanka



Mon, 17 Apr 2006 08:02:34 GMT
 Alter Table Alter Column Syntax

ALTER TABLE tblname ALTER COLUMN colname VARCHAR(20) NOT NULL
ALTER TABLE tblname ADD CONSTRAINT cnstrname DEFAULT '---' FOR colname

Before the first alter table, you would need to make sure that you have no NULL's, of course. And,
why are you changing a datetime to varchar? Sounds like something one doesn't want to do...!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...

Quote:

> Hi,

> I have a table called "Invoice" with "InvoiceDate" field
> defined to be "DataTime" data type with length 8 and Allow
> Nulls true.

> I need to change the Datatype of "InvoiceDate" field to
> Varchar(20), Not NULL with Default "---".

> Can this be done through SQL statements.

> Regards,
> Sanka



Mon, 17 Apr 2006 08:35:35 GMT
 Alter Table Alter Column Syntax
SANKA
CREATE TABLE #TEST
(
 COL1 INT NOT NULL,
 COL DATETIME NULL
)
INSERT INTO #TEST VALUES (1,GETDATE())
GO
ALTER TABLE #TEST
ALTER COLUMN COL VARCHAR(20) NOT NULL
GO
ALTER TABLE #TEST WITH NOCHECK
 ADD CONSTRAINT MY_CONSTR DEFAULT ('-----') FOR COL
GO
SELECT * FROM #TEST
INSERT INTO #TEST (COL1) VALUES (2)
DROP TABLE #TEST


Quote:
> Hi,

> I have a table called "Invoice" with "InvoiceDate" field
> defined to be "DataTime" data type with length 8 and Allow
> Nulls true.

> I need to change the Datatype of "InvoiceDate" field to
> Varchar(20), Not NULL with Default "---".

> Can this be done through SQL statements.

> Regards,
> Sanka



Mon, 17 Apr 2006 08:42:51 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Alter table alter column syntax

2. syntax for ALTER TABLE ALTER COLUMN DEFAULT

3. Syntax for using ALTER...ALTER COLUMN...

4. multiple ALTER COLUMNs in one ALTER TABLE statement?

5. Help with Alter Table Alter Column

6. Alter table alter column

7. alter table alter column

8. Alter table Alter column problem.

9. ALTER TABLE ... ALTER COLUMN IDENTITY

10. ALTER TABLE ALTER COLUMN

11. ALTER TABLE ALTER COLUMN to set Default

12. Alter table alter column text


 
Powered by phpBB® Forum Software