Updating a field with money format

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

Updating a field with money format

Author

Topic

munror
Starting Member

5 Posts

Posted - 2010-02-06 : 19:27:37

I am trying do do a calculation for pulled value from a table to get the persons Sallery. I am able to get the correct value however I am not sure how to format in order to update the Sallery field as it is of type Money.

I'd really appreciate if someone could point me in the right direction. Bascially, I need the ComputedMnthSallery to be inserted as type Money.

Thanks!

;WITH Results_TABLE AS
(
Select Top 8000 EmployeeID, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum, str((round(Col022, 2) * round(Col023, 2) * round(Col024, 2) * 52 / 12)) as ComputedMnthSallery FROM CompEmployees
)
UPDATE tb_Employee set Sallery = (
Select ComputedMnthSallery
FROM Results_TABLE
WHERE employee_no = EmployeeID
)

munror
Starting Member

5 Posts

Posted - 2010-02-06 : 19:41:06

Oh, forgot to mention that I am trying to run the query in MsSql

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:37:17

CONVERT(money, StringExpression)

munror
Starting Member

5 Posts

Posted - 2010-02-07 : 04:59:57

Thanks Kristen, I managed to get it working using CAST!

;WITH Results_TABLE AS
(
Select Top 8000 EmployeeID, ROW_NUMBER() OVER (ORDER BY EmployeeID) AS RowNum, CAST((round(Col022, 2) * round(Col023, 2) * round(Col024, 2) * 52 / 12) AS MONEY) as ComputedMnthSallery FROM CompEmployees
)
UPDATE tb_Employee set Sallery = (
Select ComputedMnthSallery
FROM Results_TABLE
WHERE employee_no = EmployeeID
)

Kristen
Test

22859 Posts

Posted - 2010-02-08 : 02:29:21

CAST(StringExpression AS MONEY) is the same as CONVERT(money, StringExpression)

CONVERT allows extra parameters (e.g. the "Style" of a conversion from Date String to DateTime, so I always use CONVERT rather than CAST to avoid mixing the two.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources