Trouble Converting INT to MONEY

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

Trouble Converting INT to MONEY

Author

Topic

wco5002
Starting Member

10 Posts

Posted - 2008-04-24 : 18:47:33

Hi, I'm working on a DB and I'm trying to write some select statements that will display an employee's pay using the CAST and CONVERT functions. However, I can never seem to get commas inserted into the results please help. Here's the SELECT statements, where am I going wrong?

SELECT
  '$' + CAST(CONVERT(MONEY, SUM(PROJECT_BUDGET/1.00), 1) AS VARCHAR) AS 'Total Budget'
FROM
  PROJECT;

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 18:51:46

You should format your data in your application and not in T-SQL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

wco5002
Starting Member

10 Posts

Posted - 2008-04-24 : 18:57:15

Well this is a lab assignment for a college course. I'm just using DB provided by the instructor, and the input is INT. Therefore, I need the output to look something like this $99,999,999.00 instead of $99999999.00, which is how it is displayed with the aforementioned SELECT statement.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-24 : 19:29:16

Does your assignment say to add commas and a dollar sign? Your instructor needs to go back to training if it does.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

wco5002
Starting Member

10 Posts

Posted - 2008-04-24 : 19:36:04

"Be sure to also format currency operations accordingly."
"1. Create a SELECT statement that returns all Employee Billing Rates in a $99,999.99 format."

Here is the code for the DB:

CREATE TABLE project_employee (
  employee_id               INT,
  employee_name             VARCHAR(40),
  employee_hire_date        DATETIME,
  employee_termination_date DATETIME,
  employee_billing_rate     INT,
  CONSTRAINT project_employee_pk
    PRIMARY KEY (employee_id)
);

CREATE TABLE project (
  project_id              INT,
  project_name            VARCHAR(40),
  project_budget          INT,
  CONSTRAINT project_pk
    PRIMARY KEY (project_id)
);

CREATE TABLE project_hours (
  project_id              INT,
  employee_id             INT,
  time_log_date           DATETIME,
  hours_logged           INT,
  dollars_charged         INT,
  CONSTRAINT project_hours_pk
    PRIMARY KEY (project_id, employee_id, time_log_date),
  CONSTRAINT proj_hours_fkto_projectemployee
    FOREIGN KEY (employee_id) REFERENCES project_employee,
  CONSTRAINT proj_hours_fkto_project
    FOREIGN KEY (project_id) REFERENCES project
);

INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1001, 'Corporate Web Site',1912000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1002, 'Year 2000 Fixes',999998000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1003, 'Accounting System Implementation',897000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1004, 'Data Warehouse Maintenance',294000);
INSERT INTO project
  (project_id, project_name, project_budget)
  VALUES (1005, 'TCP/IP Implementation',415000);



INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
INSERT INTO project_employee
  (employee_id, employee_name, employee_hire_date,
   employee_termination_date, employee_billing_rate)
  VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);


INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-JAN-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-JAN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-JAN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-FEB-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-MAR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-MAR-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-APR-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-APR-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-MAY-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-MAY-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-JUN-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-JUN-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-JUL-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-JUL-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-AUG-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-SEP-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-SEP-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-SEP-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-OCT-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1001,101,'01-NOV-1998',3.00,507.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1003,101,'01-NOV-1998',5.00,845.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1002,101,'01-DEC-1998',1.00,169.00);
INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
            VALUES (1004,101,'01-DEC-1998',3.00,507.00);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 03:01:29

[code]DECLARE @ProjectBudget INT

SET @ProjectBudget = 1000000

SELECT '$' + CONVERT(VARCHAR, CAST(@ProjectBudget AS MONEY), 1)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources