How do I set currency format of total price in SQL ?

Question

1.00/5 (1 vote)

See more:

Hi Everyone, I have SQL Code which I take the total sum out of it, but the number does not come in full format, what I want from number is to show like this ("1.500,00") and what I get is like this ("1500,00") So for this is my code:

SELECT
        Sum(Price) As Total,
        CONVERT(char(7), date, 120) As [year],
		Department 
    FROM
        dbo.Expenses
    WHERE
       CONVERT(char(7), date, 120) Between '2017-01' And '2019-01'
    And
        Department = 'Grocery' 
    GROUP BY
       CONVERT(char(7), date, 120),
	   Department

and it shows me the number without a fullstop. The number that i get is for examples 5656,75 and what i would like is to be 5.656,75

What I have tried:

This is the full code that I working on and I don't know where and how to put the format number in the SUM area.

WITH cte As
(
   SELECT
        Sum(Price) As Total,
        CONVERT(char(7), date, 120) As [year],
		Department 
    FROM
        dbo.Expenses
    WHERE
        --CONVERT(char(7), date, 120) Between '2017-01' And '2019-01'
    --And
        Department = 'Grocery' 
    GROUP BY
       CONVERT(char(7), date, 120),
	   Department 
	   )
SELECT
    Total,
    [year],Department as 'Department',
	   Concat(SUM(Total) OVER (),'€') As GrandTotal
FROM
    cte
WHERE
    [year] Is Not Null
;

Thank you for the patience and help,
Gratefully E

Posted 22-Jul-20 6:53am

Member 13410460

Updated 25-Jul-20 9:15am

v3

Add a Solution

2 solutions

Solution 2

You can create your own formatting function for SQL.

Within the function you count the number of chars, in your case, to left of decimal. You can then determine if it's >= 1000 (where you would add begin to need the thousands separator) or just return the value.

If you have to work with it then you need to convert it to a string so you can insert your thousands separator.

Here's the hard part for you: figure out how you will insert multiple separators into your string when every time you add one the length of the string changes.

Now I made one of these myself, for MS SQL:

SET @RVAL = CONVERT(VARCHAR, CAST(@number as Money), 1)

which uses a built-in function to handle this for you. Notice that it's a CAST within a CONVERT and my cast is for USA style: (commas between thousands, period to mark the decimal break).

Posted 23-Jul-20 3:26am

W∴ Balboos, GHB

Updated 23-Jul-20 3:27am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Accepted Answer · 2020-07-22T07:05:00

Solution 1

Quote:
This is the full code that I working on and I don't know where and how to put the format number in the SUM area.

Short answer: you don't format number inside SQL.
SQL returns the SUM as a number, there is no formatting at this point.
Formatting is done only when convert the number to string or when printing.

Posted 22-Jul-20 7:05am

Patrice T

Comments

Member 13410460 22-Jul-20 13:08pm

Thank you for your reply, But how do I change then the format of the number? do I have to work it out on vb.net?

Patrice T 22-Jul-20 13:10pm

If VB.net is the language of your app, yes, formatting in the prints in VB app.

Member 13410460 22-Jul-20 13:13pm

Thank you sir, I will try that now.

Member 13410460 24-Jul-20 9:54am

The Label on my form shows the direct result from SQL Total sum , so I need to format that number. I tried different ways how to do it in my vb.net but I could not find anything useful.

Patrice T 24-Jul-20 10:02am

Update your question with exact problem you have and concerned VB code.
Use Improve question to update your question.

Member 13410460 24-Jul-20 10:04am

But I dont need to change the question , I still need help regarding how to format the total sum format number in SQL.

Patrice T 24-Jul-20 10:16am

Explain exactly what you try to do, with context.
Your solution is not necessary the solution.
Also, tell which GUI component you use.

Member 13410460 24-Jul-20 10:22am

So, I use SQL to show informaton into Chart, and what I want now is that when I go to the point showed in vb.net Charts to show me the description of what has been bought that day(something that is not specified in SQL script above). Perhaps a new query that I will write to show me the description and to connect to that query through these lines :

im thisPt As New PointF(CSng(Chartday.Series("Day").Points(Result.PointIndex).XValue),
CSng(Chartday.Series("Day").Points(Result.PointIndex).YValues(0)))

or here as you mention but I dont know how to construct it :

.X = thisPt.X + 1
.Y = thisPt.Y + 1
.Text = thisPt.ToString
I work in VIsual studio 2015.
Thank you for your patience.

Patrice T 24-Jul-20 10:31am

Use Improve question to update your question.
So that everyone can pay attention to this information.

Member 13410460 24-Jul-20 10:32am

I Did it, Thank you.