Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    83

    Exclamation Unanswered: Money to Varchar

    How can I alter a field datatype from money to varchar?

  2. #2
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45
    To change the column type you will have to create a new table and copy the data over using the convert function, example below.

    --create table x
    --(a int null,
    --b money null)

    CREATE TABLE dbo.Tmp_x
    (
    a int NULL,
    b nvarchar(50) NULL
    ) ON [PRIMARY]
    GO
    IF EXISTS(SELECT * FROM dbo.x)
    EXEC('INSERT INTO dbo.Tmp_x (a, b)
    SELECT a, CONVERT(nvarchar(50), b) FROM dbo.x')
    GO
    DROP TABLE dbo.x
    GO
    EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT'
    GO

  3. #3
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    You can also add a temporary column to your table of the varchar type and fill it with the data from the money column to see if all values can be converted. If so, empty the money column, change the datatype of the money column to varchar and fill it with the data of the temporary column. Finally delete the temporary column.

    To add a column:
    Code:
    alter table owner.your_table
    add temp_column varchar(10)
    To modify a column datatype:
    Code:
    alter table owner.your_table
    alter column your_column varchar(10)
    To drop a column:
    Code:
    alter table owner.your_table
    drop column temp_column
    Last edited by jora; 05-11-04 at 03:09.
    Johan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •