CODEWITHSUNDEEP
sqlsql-server
user4190165
user4190165

Reputation:

Inserting "$" in a SELECT Statement SQL Server

I have a SELECT Statement that shows details on Orders.

It uses the below code

SELECT Orders.OrderID, Orders.invoiceID, Items.itemName AS 'Item Name', Orders.quantity, DATENAME(mm, Orders.OrderDate) + ' ' + DATENAME(dd, Orders.OrderDate) + ', ' + DATENAME(yyyy, Orders.OrderDate) AS 'Order Date', (Orders.price * Orders.quantity) AS 'Total', Orders.delivered
    FROM Orders
    INNER JOIN Items
    ON Orders.itemID = Items.itemID
    ORDER BY Orders.orderID, Items.itemID ASC

Everything works fine, however I'm not sure how to get a "$" to show up to the left of the numbers in the "total" field.

All help will be appreciated.

Thanks, Bryan

Upvotes: 1

Views: 83

Answers (3)

Mureinik
Mureinik

Reputation: 311428

You could just explicitly concat it:

SELECT     Orders.OrderID, 
           Orders.invoiceID, 
           Items.itemName AS 'Item Name', 
           Orders.quantity, 
           DATENAME(mm, Orders.OrderDate) + ' ' + DATENAME(dd, Orders.OrderDate) + ', ' + DATENAME(yyyy, Orders.OrderDate) AS 'Order Date', 
           '$' + (CAST (Orders.price * Orders.quantity) AS VARCHAR(100)) AS 'Total', 
           Orders.delivered
FROM       Orders
INNER JOIN Items ON Orders.itemID = Items.itemID
ORDER BY   Orders.orderID, Items.itemID ASC

Upvotes: 1

CharlesMighty
CharlesMighty

Reputation: 572

try this

SELECT Orders.OrderID, 
       Orders.invoiceID, 
       Items.itemName AS 'Item Name', 
       Orders.quantity, 
       DATENAME(mm, Orders.OrderDate) + ' ' + DATENAME(dd, Orders.OrderDate) + ', ' + DATENAME(yyyy, Orders.OrderDate) AS 'Order Date', 
       '$' + Convert(VARCHAR(50), Orders.price * Orders.quantity) AS 'Total', 
       Orders.delivered
FROM   Orders
INNER JOIN Items ON Orders.itemID = Items.itemID
ORDER BY   Orders.orderID, Items.itemID ASC

Upvotes: 1

Alec.
Alec.

Reputation: 5525

This is untested, but this is the same answer but casting the numeric value.

SELECT     Orders.OrderID, 
           Orders.invoiceID, 
           Items.itemName AS 'Item Name', 
           Orders.quantity, 
    DATENAME(mm, Orders.OrderDate) + ' ' + DATENAME(dd, Orders.OrderDate) + ', ' + DATENAME(yyyy,          Orders.OrderDate) AS 'Order Date', 
    '$' + CAST((Orders.price * Orders.quantity) as varchar(90))
          AS 'Total', 
           Orders.delivered
FROM       Orders
INNER JOIN Items ON Orders.itemID = Items.itemID
ORDER BY   Orders.orderID, Items.itemID ASC

Upvotes: 0

Related Questions