Convert Decimal Numbers to Money with cents / without cents in SQL

In this article, I will demonstrate

How to Convert Decimal Numbers to Money with cents / without cents in SQL.


Scenario

I have a decimal number like  71567536.100000, I need to convert it to money format

  • with cents to be like this 71,567,536.10 ر.س
  • without cents to be like this  71,567,536 ر.س

Convert Decimal To Money With Cents In SQL Server

You can use one of the below quires


SELECT STUFF( convert(varchar, convert(money,71567536.100000),1) , 1, 0,'.') + N' ر.س ' as 'Budget'


SELECT convert(varchar(100), cast(71567536.100000 as money), 1)+ N' ر.س ' as 'Budget'

Output

Convert Decimal To Money With Cents In SQL Server

Convert Decimal To Money Without Cents In SQL Server

You can use one of the below quires


SELECT parsename( convert(varchar(100), cast(71567536.100000 as money), 1),2) + N' ر.س ' as 'Budget'

 SELECT replace( convert(varchar, cast(floor(71567536.100000) as money),1), '.00', '') + N' ر.س ' as 'Budget' 

Note: The second statement faster than the first with 21 NS.

Convert Decimal To Money Without Cents In SQL Server


Applies To
  • SQL Server.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s