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

In this hint, I will demonstrate How to Convert Decimal Numbers to Money with cents / without cents in SQL.

Example:

  • 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 ر.س

Steps:

With respect to Convert Decimal To Money With Cents

You can use the following query

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

Or

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

convertwithcents

With respect to Convert Decimal To Money With Cents

You can use the following query

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

Or

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.

convertwithoutcents

Enjoy 🙂

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s