Slashing last the three zeros on a currency field

• Clement Mboma

  Newbie

  Points: 1

  More actions

  August 3, 2006 at 3:45 am

  #169902

  We have been told to slash the last three zeros of our currency by the authorities. I am trying to come out with a script that will locate every currency field in the database and divide it by 1000 to comply with the new regulation e.g if database value is $1000 it should be reduced to $1. Can someone drop me a sample script that can achieve that?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  August 3, 2006 at 4:43 am

  #653028

  Clement

  Assuming all currency fields in your database have the "money" data type, this will generate a script that will change your values as required.

  John

  SELECT 'UPDATE ' + c.TABLE_NAME + ' SET ' + c.COLUMN_NAME + ' = ' + c.COLUMN_NAME + '/1000.000'

  FROM INFORMATION_SCHEMA.COLUMNS c JOIN INFORMATION_SCHEMA.TABLES t

    ON c.TABLE_NAME = t.TABLE_NAME

  WHERE c.DATA_TYPE = 'money'

    AND t.TABLE_TYPE = 'BASE TABLE'