Problem
When working with currency formats, there are many different money formats used around the world. For example, in the USA we use the $ for the currency symbol and a decimal point as a separator for money. Whereas in Latin America, we use the decimal point instead of a comma as the separator. Is there a simple way to handle the format of different currencies in Microsoft SQL Server?
Solution
In this tutorial, we will show different ways to output different currency formats with the T-SQL language.
FORMAT Function to SQL Format Currency
You can change the currency format using the SQL Server FORMAT function. This function allows you to format currency output. The following example will show how to work with a different number of decimal places:
| Number Format | Syntax | Output |
|---|---|---|
| Default currency value | SELECT FORMAT(200.3639, ‘C’) AS ‘Currency Format’ | $200.36 |
| Currency, 3 decimals | SELECT FORMAT(200.3639, ‘C3’) AS ‘Currency Format’ | $200.364 |
| Currency, 4 decimals | SELECT FORMAT(200.3639, ‘C4’, ‘en-us’) AS ‘Currency Format’ | $200.3639 |
Custom Format Currency Options
Here are other ways to have custom SQL currency format output. In these examples we use different SQL Server functions to change the output.
| Number Format | Syntax | Output |
|---|---|---|
| Use REPLACE to change currency symbol | SELECT REPLACE(FORMAT(200.36, ‘C’),’$’,’£’) AS ‘Currency Format’ | £200.36 |
| Custom format with spaces | SELECT FORMAT(200.3639,’$### . ####’) AS ‘Currency Format’; | $200 . 3639 |
| Change period to comma | SELECT REPLACE(FORMAT(200.36, ‘C’, ‘en-us’),’.’,’,’) AS ‘Currency Format’ | $200,36 |
SQL FORMAT Function to Format Currency with Culture
There are hundreds of cultures already defined for SQL databases and the below output shows how the FORMAT function can be used and what the local money output looks like.
| Language | Syntax | Output |
|---|---|---|
| Afrikaans – South Africa | SELECT FORMAT (200.364, ‘c’, ‘af-ZA’) as date | R200,36 |
| Albanian – Albania | SELECT FORMAT (200.364, ‘c’, ‘sq-AL’) as date | 200 Lekë |
| Alsatian – France | SELECT FORMAT (200.364, ‘c’, ‘gsw-FR’) as date | 200,36 € |
| Amharic – Ethiopia | SELECT FORMAT (200.364, ‘c’, ‘am-ET’) as date | ብር200.36 |
| Arabic – Algeria | SELECT FORMAT (200.364, ‘c’, ‘ar-DZ’) as date | 200.36 د.ج. |
| Arabic – Bahrain | SELECT FORMAT (200.364, ‘c’, ‘ar-BH’) as date | 200.364 د.ب. |
| Arabic – Egypt | SELECT FORMAT (200.364, ‘c’, ‘ar-EG’) as date | 200.36 ج.م. |
| Arabic – Iraq | SELECT FORMAT (200.364, ‘c’, ‘ar-IQ’) as date | 200.36 د.ع. |
| Arabic – Jordan | SELECT FORMAT (200.364, ‘c’, ‘ar-JO’) as date | 200.364 د.ا. |
| Arabic – Kuwait | SELECT FORMAT (200.364, ‘c’, ‘ar-KW’) as date | 200.364 د.ك. |
| Arabic – Lebanon | SELECT FORMAT (200.364, ‘c’, ‘ar-LB’) as date | 200.36 ل.ل. |
| Arabic – Libya | SELECT FORMAT (200.364, ‘c’, ‘ar-LY’) as date | 200.364 د.ل. |
| Arabic – Morocco | SELECT FORMAT (200.364, ‘c’, ‘ar-MA’) as date | 200.36 د.م. |
| Arabic – Oman | SELECT FORMAT (200.364, ‘c’, ‘ar-OM’) as date | 200.364 ر.ع. |
| Arabic – Qatar | SELECT FORMAT (200.364, ‘c’, ‘ar-QA’) as date | 200.36 ر.ق. |
| Arabic – Saudi Arabia | SELECT FORMAT (200.364, ‘c’, ‘ar-SA’) as date | 200.36 ر.س. |
| Arabic – Syria | SELECT FORMAT (200.364, ‘c’, ‘ar-SY’) as date | 200.36 ل.س. |
| Arabic – Tunisia | SELECT FORMAT (200.364, ‘c’, ‘ar-TN’) as date | 200.364 د.ت. |
| Arabic – U.A.E. | SELECT FORMAT (200.364, ‘c’, ‘ar-AE’) as date | 200.36 د.إ. |
| Arabic – Yemen | SELECT FORMAT (200.364, ‘c’, ‘ar-YE’) as date | 200.36 ر.ي. |
| Armenian – Armenia | SELECT FORMAT (200.364, ‘c’, ‘hy-AM’) as date | 200.36 ֏ |
| Assamese – India | SELECT FORMAT (200.364, ‘c’, ‘as-IN’) as date | ₹ 200.36 |
| Azerbaijani – Azerbaijan (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘az-Cyrl-AZ’) as date | 200,36 ₼ |
| Azerbaijani – Azerbaijan (Latin) | SELECT FORMAT (200.364, ‘c’, ‘az-Latn-AZ’) as date | 200,36 ₼ |
| Bangla (Bangladesh) | SELECT FORMAT (200.364, ‘c’, ‘bn-BD’) as date | 200.36৳ |
| Bangla – India (Bengali Script) | SELECT FORMAT (200.364, ‘c’, ‘bn-IN’) as date | ₹ 200.36 |
| Bashkir – Russia | SELECT FORMAT (200.364, ‘c’, ‘ba-RU’) as date | 200,36 ₽ |
| Basque – Basque | SELECT FORMAT (200.364, ‘c’, ‘eu-ES’) as date | 200,36 € |
| Belarusian – Belarus | SELECT FORMAT (200.364, ‘c’, ‘be-BY’) as date | 200,36 Br |
| Bosnian – Bosnia and Herzegovina (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘bs-Cyrl-BA’) as date | 200,36 КМ |
| Bosnian – Bosnia and Herzegovina (Latin) | SELECT FORMAT (200.364, ‘c’, ‘bs-Latn-BA’) as date | 200,36 KM |
| Breton – France | SELECT FORMAT (200.364, ‘c’, ‘br-FR’) as date | 200,36 € |
| Bulgarian – Bulgaria | SELECT FORMAT (200.364, ‘c’, ‘bg-BG’) as date | 200,36 лв. |
| Burmese – Myanmar | SELECT FORMAT (200.364, ‘c’, ‘my-MM’) as date | 200 K |
| Catalan – Catalan | SELECT FORMAT (200.364, ‘c’, ‘ca-ES’) as date | 200,36 € |
| Central Atlas Tamazight (Latin) – Algeria | SELECT FORMAT (200.364, ‘c’, ‘fr-FR’) as date | 200,36 € |
| Central Atlas Tamazight (Latin) – Algeria | SELECT FORMAT (200.364, ‘c’, ‘tzm-Latn-DZ’) as date | 200,36 DA |
| Central Atlas Tamazight (Tifinagh) – Morocco | SELECT FORMAT (200.364, ‘c’, ‘tzm-Tfng-MA’) as date | 200,36 ⴷⵔ |
| Central Kurdish (Iraq) | SELECT FORMAT (200.364, ‘c’, ‘ku-Arab-IQ’) as date | د.ع.200.36 |
| Cherokee (Cherokee, United States) | SELECT FORMAT (200.364, ‘c’, ‘chr-Cher-US’) as date | $200.36 |
| Chinese (Simplified, China) | SELECT FORMAT (200.364, ‘c’, ‘zh-CN’) as date | ¥200.36 |
| Chinese – (Traditional, Taiwan) | SELECT FORMAT (200.364, ‘c’, ‘zh-TW’) as date | NT$200.36 |
| Corsican – France | SELECT FORMAT (200.364, ‘c’, ‘co-FR’) as date | 200,36 € |
| Croatian – Bosnia and Herzegovina | SELECT FORMAT (200.364, ‘c’, ‘hr-BA’) as date | 200,36 KM |
| Croatian – Croatia | SELECT FORMAT (200.364, ‘c’, ‘hr-HR’) as date | 200,36 kn |
| Czech – Czech Republic | SELECT FORMAT (200.364, ‘c’, ‘cs-CZ’) as date | 200,36 Kč |
| Danish – Denmark | SELECT FORMAT (200.364, ‘c’, ‘da-DK’) as date | 200,36 kr. |
| Dari – Afghanistan | SELECT FORMAT (200.364, ‘c’, ‘prs-AF’) as date | ؋200.36 |
| Divehi – Maldives | SELECT FORMAT (200.364, ‘c’, ‘dv-MV’) as date | 200.36 ރ. |
| Dutch – Belgium | SELECT FORMAT (200.364, ‘c’, ‘nl-BE’) as date | € 200,36 |
| Dutch – Netherlands | SELECT FORMAT (200.364, ‘c’, ‘nl-NL’) as date | € 200,36 |
| Dzongkha | SELECT FORMAT (200.364, ‘c’, ‘dz-BT’) as date | Nu.200.36 |
| English – Australia | SELECT FORMAT (200.364, ‘c’, ‘en-AU’) as date | $200.36 |
| English – Belize | SELECT FORMAT (200.364, ‘c’, ‘en-BZ’) as date | $200.36 |
| English – Canada | SELECT FORMAT (200.364, ‘c’, ‘en-CA’) as date | $200.36 |
| English – Caribbean | SELECT FORMAT (200.364, ‘c’, ‘en-029’) as date | EC$200.36 |
| English – India | SELECT FORMAT (200.364, ‘c’, ‘en-IN’) as date | ₹ 200.36 |
| English – Ireland | SELECT FORMAT (200.364, ‘c’, ‘en-IE’) as date | €200.36 |
| English – Jamaica | SELECT FORMAT (200.364, ‘c’, ‘en-JM’) as date | $200.36 |
| English – Malaysia | SELECT FORMAT (200.364, ‘c’, ‘en-MY’) as date | RM200.36 |
| English – New Zealand | SELECT FORMAT (200.364, ‘c’, ‘en-NZ’) as date | $200.36 |
| English – Philippines | SELECT FORMAT (200.364, ‘c’, ‘en-PH’) as date | ₱200.36 |
| English – Singapore | SELECT FORMAT (200.364, ‘c’, ‘en-SG’) as date | $200.36 |
| English – South Africa | SELECT FORMAT (200.364, ‘c’, ‘en-ZA’) as date | R200,36 |
| English – Trinidad | SELECT FORMAT (200.364, ‘c’, ‘en-TT’) as date | $200.36 |
| English – Great Britain | SELECT FORMAT (200.364, ‘c’, ‘en-GB’) as date | £200.36 |
| English – United States | SELECT FORMAT (200.364, ‘c’, ‘en-US’) as date | $200.36 |
| English – Zimbabwe | SELECT FORMAT (200.364, ‘c’, ‘en-ZW’) as date | US$200.36 |
| Estonian – Estonia | SELECT FORMAT (200.364, ‘c’, ‘et-EE’) as date | 200,36 € |
| Faroese – Faroe Islands | SELECT FORMAT (200.364, ‘c’, ‘fo-FO’) as date | 200,36 kr |
| Filipino – Philippines | SELECT FORMAT (200.364, ‘c’, ‘fil-PH’) as date | ₱200.36 |
| Finnish – Finland | SELECT FORMAT (200.364, ‘c’, ‘fi-FI’) as date | 200,36 € |
| French – Belgium | SELECT FORMAT (200.364, ‘c’, ‘fr-BE’) as date | 200,36 € |
| French – Canada | SELECT FORMAT (200.364, ‘c’, ‘fr-CA’) as date | 200,36 $ |
| French – France | SELECT FORMAT (200.364, ‘c’, ‘fr-FR’) as date | 200,36 € |
| French – Luxembourg | SELECT FORMAT (200.364, ‘c’, ‘fr-LU’) as date | 200,36 € |
| French – Monaco | SELECT FORMAT (200.364, ‘c’, ‘fr-MC’) as date | 200,36 € |
| French – Switzerland | SELECT FORMAT (200.364, ‘c’, ‘fr-CH’) as date | 200.36 CHF |
| Frisian – Netherlands | SELECT FORMAT (200.364, ‘c’, ‘fy-NL’) as date | € 200,36 |
| Fulah (Latin, Senegal) | SELECT FORMAT (200.364, ‘c’, ‘ff-Latn-SN’) as date | 200,36 CFA |
| Galician – Galician | SELECT FORMAT (200.364, ‘c’, ‘gl-ES’) as date | 200,36 € |
| Georgian – Georgia | SELECT FORMAT (200.364, ‘c’, ‘ka-GE’) as date | 200,36 ₾ |
| German – Austria | SELECT FORMAT (200.364, ‘c’, ‘de-AT’) as date | € 200,36 |
| German – Germany | SELECT FORMAT (200.364, ‘c’, ‘de-DE’) as date | 200,36 € |
| German – Liechtenstein | SELECT FORMAT (200.364, ‘c’, ‘de-LI’) as date | CHF 200.36 |
| German – Luxembourg | SELECT FORMAT (200.364, ‘c’, ‘de-LU’) as date | 200,36 € |
| German – Switzerland | SELECT FORMAT (200.364, ‘c’, ‘de-CH’) as date | CHF 200.36 |
| Greek – Greece | SELECT FORMAT (200.364, ‘c’, ‘el-GR’) as date | 200,36 € |
| Greenlandic – Greenland | SELECT FORMAT (200.364, ‘c’, ‘kl-GL’) as date | kr.200,36 |
| Guarani – Paraguay | SELECT FORMAT (200.364, ‘c’, ‘gn-PY’) as date | 200 ₲ |
| Gujarati – India (Gujarati Script) | SELECT FORMAT (200.364, ‘c’, ‘gu-IN’) as date | ₹200.36 |
| Hausa (Latin) – Nigeria | SELECT FORMAT (200.364, ‘c’, ‘ha-Latn-NG’) as date | ₦ 200.36 |
| Hawaiian – United States | SELECT FORMAT (200.364, ‘c’, ‘haw-US’) as date | $200.36 |
| Hebrew – Israel | SELECT FORMAT (200.364, ‘c’, ‘he-IL’) as date | ₪ 200.36 |
| Hindi – India | SELECT FORMAT (200.364, ‘c’, ‘hi-IN’) as date | ₹200.36 |
| Hungarian – Hungary | SELECT FORMAT (200.364, ‘c’, ‘hu-HU’) as date | 200,36 Ft |
| Icelandic – Iceland | SELECT FORMAT (200.364, ‘c’, ‘is-IS’) as date | 200 kr |
| Igbo – Nigeria | SELECT FORMAT (200.364, ‘c’, ‘ig-NG’) as date | ₦200.36 |
| Inari Sami – Finland | SELECT FORMAT (200.364, ‘c’, ‘smn-FI’) as date | 200,36 € |
| Indonesian – Indonesia | SELECT FORMAT (200.364, ‘c’, ‘id-ID’) as date | Rp200,36 |
| Inuktitut (Latin) – Canada | SELECT FORMAT (200.364, ‘c’, ‘iu-Latn-CA’) as date | $200.36 |
| Inuktitut (Syllabics) – Canada | SELECT FORMAT (200.364, ‘c’, ‘iu-Cans-CA’) as date | $200.36 |
| Irish – Ireland | SELECT FORMAT (200.364, ‘c’, ‘ga-IE’) as date | €200.36 |
| isiXhosa / Xhosa – South Africa | SELECT FORMAT (200.364, ‘c’, ‘xh-ZA’) as date | R200.36 |
| isiZulu / Zulu – South Africa | SELECT FORMAT (200.364, ‘c’, ‘zu-ZA’) as date | R200.36 |
| Italian – Italy | SELECT FORMAT (200.364, ‘c’, ‘it-IT’) as date | 200,36 € |
| Italian – Switzerland | SELECT FORMAT (200.364, ‘c’, ‘it-CH’) as date | CHF 200.36 |
| Japanese – Japan | SELECT FORMAT (200.364, ‘c’, ‘ja-JP’) as date | ¥200 |
| Javanese (Latin) – Indonesia | SELECT FORMAT (200.364, ‘c’, ‘jv-Latn-ID’) as date | Rp200 |
| Kannada – India (Kannada Script) | SELECT FORMAT (200.364, ‘c’, ‘kn-IN’) as date | ₹200.36 |
| Kazakh – Kazakhstan | SELECT FORMAT (200.364, ‘c’, ‘kk-KZ’) as date | 200,36 ₸ |
| Khmer – Cambodia | SELECT FORMAT (200.364, ‘c’, ‘km-KH’) as date | 200.36៛ |
| K’iche – Guatemala | SELECT FORMAT (200.364, ‘c’, ‘qut-GT’) as date | Q200.36 |
| Kinyarwanda – Rwanda | SELECT FORMAT (200.364, ‘c’, ‘rw-RW’) as date | RF 200 |
| Konkani – India | SELECT FORMAT (200.364, ‘c’, ‘kok-IN’) as date | ₹ 200.36 |
| Korean(Extended Wansung) – Korea | SELECT FORMAT (200.364, ‘c’, ‘ko-KR’) as date | ₩200 |
| Kyrgyz – Kyrgyzstan | SELECT FORMAT (200.364, ‘c’, ‘ky-KG’) as date | 200,36 сом |
| Lao – Lao PDR | SELECT FORMAT (200.364, ‘c’, ‘lo-LA’) as date | ₭200 |
| Latvian – Legacy | SELECT FORMAT (200.364, ‘c’, ‘lv-LV’) as date | 200,36 € |
| Latvian – Standard | SELECT FORMAT (200.364, ‘c’, ‘lv-LV’) as date | 200,36 € |
| Lithuanian – Lithuania | SELECT FORMAT (200.364, ‘c’, ‘lt-LT’) as date | 200,36 € |
| Lower Sorbian – Germany | SELECT FORMAT (200.364, ‘c’, ‘dsb-DE’) as date | 200,36 € |
| Lule Sami – Norway | SELECT FORMAT (200.364, ‘c’, ‘smj-NO’) as date | kr 200,36 |
| Lule Sami – Sweden | SELECT FORMAT (200.364, ‘c’, ‘smj-SE’) as date | 200,36 kr |
| Luxembourgish – Luxembourg | SELECT FORMAT (200.364, ‘c’, ‘lb-LU’) as date | 200,36 € |
| Macedonian – F.Y.R.O.M | SELECT FORMAT (200.364, ‘c’, ‘mk-MK’) as date | 200,36 ден |
| Malay – Brunei | SELECT FORMAT (200.364, ‘c’, ‘ms-BN’) as date | $ 200,36 |
| Malay – Malaysia | SELECT FORMAT (200.364, ‘c’, ‘ms-MY’) as date | RM200.36 |
| Malayalam – India (Malayalam Script) | SELECT FORMAT (200.364, ‘c’, ‘ml-IN’) as date | ₹200.36 |
| Maltese – Malta | SELECT FORMAT (200.364, ‘c’, ‘mt-MT’) as date | €200.36 |
| Maori – New Zealand | SELECT FORMAT (200.364, ‘c’, ‘mi-NZ’) as date | $200.36 |
| Mapudungun – Chile | SELECT FORMAT (200.364, ‘c’, ‘arn-CL’) as date | $ 200,36 |
| Marathi – India | SELECT FORMAT (200.364, ‘c’, ‘mr-IN’) as date | ₹200`36 |
| Mohawk – Mohawk | SELECT FORMAT (200.364, ‘c’, ‘moh-CA’) as date | $200.36 |
| Mongolian (Cyrillic) – Mongolia | SELECT FORMAT (200.364, ‘c’, ‘mn-MN’) as date | ₮ 200.36 |
| Mongolian (Mongolian) – Mongolia | SELECT FORMAT (200.364, ‘c’, ‘mn-Mong-MN’) as date | ₮200.36 |
| Mongolian (Traditional Mongolian – China – Legacy) | SELECT FORMAT (200.364, ‘c’, ‘mn-Mong-CN’) as date | ¥200.36 |
| Mongolian (Traditional Mongolian – China – Standard) | SELECT FORMAT (200.364, ‘c’, ‘mn-Mong-CN’) as date | ¥200.36 |
| N’ko – Guinea | SELECT FORMAT (200.364, ‘c’, ‘nqo-GN’) as date | ߖߕ. 200.36 |
| Nepali – Federal Democratic Republic of Nepal | SELECT FORMAT (200.364, ‘c’, ‘ne-NP’) as date | रु 200.36 |
| Northern Sami – Finland | SELECT FORMAT (200.364, ‘c’, ‘se-FI’) as date | 200,36 € |
| Northern Sami – Norway | SELECT FORMAT (200.364, ‘c’, ‘se-NO’) as date | 200,36 kr |
| Northern Sami – Sweden | SELECT FORMAT (200.364, ‘c’, ‘se-SE’) as date | 200,36 kr |
| Norwegian – Norway (Bokmål) | SELECT FORMAT (200.364, ‘c’, ‘nb-NO’) as date | kr 200,36 |
| Norwegian – Norway (Nynorsk) | SELECT FORMAT (200.364, ‘c’, ‘nn-NO’) as date | 200,36 kr |
| Occitan – France | SELECT FORMAT (200.364, ‘c’, ‘oc-FR’) as date | 200,36 € |
| Odia – India (Odia Script) | SELECT FORMAT (200.364, ‘c’, ‘or-IN’) as date | ₹ 200.36 |
| Pashto – Afghanistan | SELECT FORMAT (200.364, ‘c’, ‘ps-AF’) as date | 200 ؋ |
| Persian | SELECT FORMAT (200.364, ‘c’, ‘fa-IR’) as date | 200/36ريال |
| Polish – Poland | SELECT FORMAT (200.364, ‘c’, ‘pl-PL’) as date | 200,36 zł |
| Portuguese – Brazil | SELECT FORMAT (200.364, ‘c’, ‘pt-BR’) as date | R$ 200,36 |
| Portuguese – Portugal | SELECT FORMAT (200.364, ‘c’, ‘pt-PT’) as date | 200,36 € |
| Punjabi – India (Gurmukhi Script) | SELECT FORMAT (200.364, ‘c’, ‘pa-IN’) as date | ₹ 200.36 |
| Punjabi (Islamic Republic of Pakistan) | SELECT FORMAT (200.364, ‘c’, ‘pa-Arab-PK’) as date | Rs 200.36 |
| Quechua – Bolivia | SELECT FORMAT (200.364, ‘c’, ‘quz-BO’) as date | Bs. 200,36 |
| Quechua – Ecuador | SELECT FORMAT (200.364, ‘c’, ‘quz-EC’) as date | $ 200,36 |
| Quechua – Peru | SELECT FORMAT (200.364, ‘c’, ‘quz-PE’) as date | S/ 200.36 |
| Romanian – Romania | SELECT FORMAT (200.364, ‘c’, ‘ro-RO’) as date | 200,36 lei |
| Romansh – Switzerland | SELECT FORMAT (200.364, ‘c’, ‘rm-CH’) as date | 200.36 CHF |
| Russian – Russia | SELECT FORMAT (200.364, ‘c’, ‘ru-RU’) as date | 200,36 ₽ |
| Sakha – Russia | SELECT FORMAT (200.364, ‘c’, ‘sah-RU’) as date | 200,36 ₽ |
| Sanskrit – India | SELECT FORMAT (200.364, ‘c’, ‘sa-IN’) as date | ₹ 200.36 |
| Scottish Gaelic – Great Britain | SELECT FORMAT (200.364, ‘c’, ‘gd-GB’) as date | £200.36 |
| Serbian – Bosnia and Herzegovina (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘sr-Cyrl-BA’) as date | 200,36 КМ |
| Serbian – Bosnia and Herzegovina (Latin) | SELECT FORMAT (200.364, ‘c’, ‘sr-Latn-BA’) as date | 200,36 KM |
| Serbian – Montenegro (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘sr-Cyrl-ME’) as date | 200,36 € |
| Serbian – Montenegro (Latin) | SELECT FORMAT (200.364, ‘c’, ‘sr-Latn-ME’) as date | 200,36 € |
| Serbian – Serbia (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘sr-Cyrl-RS’) as date | 200,36 дин. |
| Serbian – Serbia (Latin) | SELECT FORMAT (200.364, ‘c’, ‘sr-Latn-RS’) as date | 200 RSD |
| Serbian – Serbia and Montenegro (Former) (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘sr-Cyrl-CS’) as date | 200,36 дин. |
| Serbian – Serbia and Montenegro (Former) (Latin) | SELECT FORMAT (200.364, ‘c’, ‘sr-Latn-CS’) as date | 200,36 din. |
| Sesotho sa Leboa / Northern Sotho – South Africa | SELECT FORMAT (200.364, ‘c’, ‘nso-ZA’) as date | R 200.36 |
| Setswana / Tswana – Botswana | SELECT FORMAT (200.364, ‘c’, ‘tn-BW’) as date | P200.36 |
| Setswana / Tswana – South Africa | SELECT FORMAT (200.364, ‘c’, ‘tn-ZA’) as date | R200.36 |
| Shona – Zimbabwe | SELECT FORMAT (200.364, ‘c’, ‘sn-Latn-ZW’) as date | $200.36 |
| Sindhi (Islamic Republic of Pakistan) | SELECT FORMAT (200.364, ‘c’, ‘sd-Arab-PK’) as date | Rs 200.36 |
| Sinhala – Sri Lanka | SELECT FORMAT (200.364, ‘c’, ‘si-LK’) as date | රු.200.36 |
| Skolt Sami – Finland | SELECT FORMAT (200.364, ‘c’, ‘sms-FI’) as date | 200,36 € |
| Slovak – Slovakia | SELECT FORMAT (200.364, ‘c’, ‘sk-SK’) as date | 200,36 € |
| Slovenian – Slovenia | SELECT FORMAT (200.364, ‘c’, ‘sl-SI’) as date | 200,36 € |
| Southern Sami – Norway | SELECT FORMAT (200.364, ‘c’, ‘sma-NO’) as date | kr 200,36 |
| Southern Sami – Sweden | SELECT FORMAT (200.364, ‘c’, ‘sma-SE’) as date | 200,36 kr |
| Spanish – Argentina | SELECT FORMAT (200.364, ‘c’, ‘es-AR’) as date | $ 200,36 |
| Spanish – Bolivarian Republic of Venezuela | SELECT FORMAT (200.364, ‘c’, ‘es-VE’) as date | Bs.S200,36 |
| Spanish – Bolivia | SELECT FORMAT (200.364, ‘c’, ‘es-BO’) as date | Bs200,36 |
| Spanish – Chile | SELECT FORMAT (200.364, ‘c’, ‘es-CL’) as date | $200 |
| Spanish – Colombia | SELECT FORMAT (200.364, ‘c’, ‘es-CO’) as date | $ 200,36 |
| Spanish – Costa Rica | SELECT FORMAT (200.364, ‘c’, ‘es-CR’) as date | ₡200,36 |
| Spanish – Dominican Republic | SELECT FORMAT (200.364, ‘c’, ‘es-DO’) as date | $200.36 |
| Spanish – Ecuador | SELECT FORMAT (200.364, ‘c’, ‘es-EC’) as date | $200,36 |
| Spanish – El Salvador | SELECT FORMAT (200.364, ‘c’, ‘es-SV’) as date | $200.36 |
| Spanish – Guatemala | SELECT FORMAT (200.364, ‘c’, ‘es-GT’) as date | Q200.36 |
| Spanish – Honduras | SELECT FORMAT (200.364, ‘c’, ‘es-HN’) as date | L200.36 |
| Spanish – Latin America | SELECT FORMAT (200.364, ‘c’, ‘es-419’) as date | XDR200.36 |
| Spanish – Mexico | SELECT FORMAT (200.364, ‘c’, ‘es-MX’) as date | $200.36 |
| Spanish – Nicaragua | SELECT FORMAT (200.364, ‘c’, ‘es-NI’) as date | C$200.36 |
| Spanish – Panama | SELECT FORMAT (200.364, ‘c’, ‘es-PA’) as date | B/.200.36 |
| Spanish – Paraguay | SELECT FORMAT (200.364, ‘c’, ‘es-PY’) as date | ₲ 200 |
| Spanish – Peru | SELECT FORMAT (200.364, ‘c’, ‘es-PE’) as date | S/ 200.36 |
| Spanish – Commonwealth of Puerto Rico | SELECT FORMAT (200.364, ‘c’, ‘es-PR’) as date | $200.36 |
| Spanish – Spain (International Sort) | SELECT FORMAT (200.364, ‘c’, ‘es-ES’) as date | 200,36 € |
| Spanish – Spain (Traditional Sort) | SELECT FORMAT (200.364, ‘c’, ‘es-ES_tradnl’) as date | 200,36 € |
| Spanish – United States | SELECT FORMAT (200.364, ‘c’, ‘es-US’) as date | $200.36 |
| Spanish – Uruguay | SELECT FORMAT (200.364, ‘c’, ‘es-UY’) as date | $ 200,36 |
| Standard Moroccan Tamazight – Morocco | SELECT FORMAT (200.364, ‘c’, ‘zgh-Tfng-MA’) as date | 200,36MAD |
| Swahili – Kenya | SELECT FORMAT (200.364, ‘c’, ‘sw-KE’) as date | Ksh 200.36 |
| Swedish – Finland | SELECT FORMAT (200.364, ‘c’, ‘sv-FI’) as date | 200,36 € |
| Swedish – Sweden | SELECT FORMAT (200.364, ‘c’, ‘sv-SE’) as date | 200,36 kr |
| Syriac – Syria | SELECT FORMAT (200.364, ‘c’, ‘syr-SY’) as date | 200.36 ܠ.ܣ. |
| Tajik – Tajikistan | SELECT FORMAT (200.364, ‘c’, ‘tg-Cyrl-TJ’) as date | 200,36 смн |
| Tamil – India | SELECT FORMAT (200.364, ‘c’, ‘ta-IN’) as date | ₹ 200.36 |
| Tamil – Sri Lanka | SELECT FORMAT (200.364, ‘c’, ‘ta-LK’) as date | Rs. 200.36 |
| Tatar – Russia (Legacy) | SELECT FORMAT (200.364, ‘c’, ‘tt-RU’) as date | 200,36 ₽ |
| Tatar – Russia (Standard) | SELECT FORMAT (200.364, ‘c’, ‘tt-RU’) as date | 200,36 ₽ |
| Telugu – India (Telugu Script) | SELECT FORMAT (200.364, ‘c’, ‘te-IN’) as date | ₹200.36 |
| Thai – Thailand | SELECT FORMAT (200.364, ‘c’, ‘th-TH’) as date | ฿200.36 |
| Tibetan – China | SELECT FORMAT (200.364, ‘c’, ‘bo-CN’) as date | ¥200.36 |
| Tigrinya (Eritrea) | SELECT FORMAT (200.364, ‘c’, ‘ti-ET’) as date | Br200.36 |
| Tigrinya (Ethiopia) | SELECT FORMAT (200.364, ‘c’, ‘ti-ET’) as date | Br200.36 |
| Turkish – Turkey | SELECT FORMAT (200.364, ‘c’, ‘tr-TR’) as date | ₺200,36 |
| Turkmen – Turkmenistan | SELECT FORMAT (200.364, ‘c’, ‘tk-TM’) as date | 200,36m. |
| Ukrainian – Ukraine | SELECT FORMAT (200.364, ‘c’, ‘uk-UA’) as date | 200,36 ₴ |
| Upper Sorbian – Germany | SELECT FORMAT (200.364, ‘c’, ‘hsb-DE’) as date | 200,36 € |
| Urdu – India | SELECT FORMAT (200.364, ‘c’, ‘ur-IN’) as date | ₹200.36 |
| Urdu (Islamic Republic of Pakistan) | SELECT FORMAT (200.364, ‘c’, ‘ur-PK’) as date | Rs 200.36 |
| Uyghur – China | SELECT FORMAT (200.364, ‘c’, ‘ug-CN’) as date | ¥200.36 |
| Uzbek – Uzbekistan (Cyrillic) | SELECT FORMAT (200.364, ‘c’, ‘uz-Cyrl-UZ’) as date | 200,36 сўм |
| Uzbek – Uzbekistan (Latin) | SELECT FORMAT (200.364, ‘c’, ‘uz-Latn-UZ’) as date | 200,36 soʻm |
| Valencian – Valencia | SELECT FORMAT (200.364, ‘c’, ‘ca-ES-valencia’) as date | 200,36 € |
| Vietnamese – Vietnam | SELECT FORMAT (200.364, ‘c’, ‘vi-VN’) as date | 200,36 ₫ |
| Welsh – Great Britain | SELECT FORMAT (200.364, ‘c’, ‘cy-GB’) as date | £200.36 |
| Wolof – Senegal | SELECT FORMAT (200.364, ‘c’, ‘wo-SN’) as date | 200,36 CFA |
| Yi – China | SELECT FORMAT (200.364, ‘c’, ‘ii-CN’) as date | ¥200.36 |
Script to Recreate Currency Format List
The below script will create a temporary table to store the language and language code and then use this to display all of the currency or money formats for the given value.
CREATE TABLE #temp ([language] nvarchar(100), [langcode] nvarchar(20))
INSERT INTO #temp
VALUES('Afrikaans - South Africa','af-ZA')
,('Albanian - Albania','sq-AL')
,('Alsatian - France','gsw-FR')
,('Amharic - Ethiopia','am-ET')
,('Arabic - Algeria','ar-DZ')
,('Arabic - Bahrain','ar-BH')
,('Arabic - Egypt','ar-EG')
,('Arabic - Iraq','ar-IQ')
,('Arabic - Jordan','ar-JO')
,('Arabic - Kuwait','ar-KW')
,('Arabic - Lebanon','ar-LB')
,('Arabic - Libya','ar-LY')
,('Arabic - Morocco','ar-MA')
,('Arabic - Oman','ar-OM')
,('Arabic - Qatar','ar-QA')
,('Arabic - Saudi Arabia','ar-SA')
,('Arabic - Syria','ar-SY')
,('Arabic - Tunisia','ar-TN')
,('Arabic - U.A.E.','ar-AE')
,('Arabic - Yemen','ar-YE')
,('Armenian - Armenia','hy-AM')
,('Assamese - India','as-IN')
,('Azerbaijani - Azerbaijan (Cyrillic)','az-Cyrl-AZ')
,('Azerbaijani - Azerbaijan (Latin)','az-Latn-AZ')
,('Bangla (Bangladesh)','bn-BD')
,('Bangla - India (Bengali Script)','bn-IN')
,('Bashkir - Russia','ba-RU')
,('Basque - Basque','eu-ES')
,('Belarusian - Belarus','be-BY')
,('Bosnian - Bosnia and Herzegovina (Cyrillic)','bs-Cyrl-BA')
,('Bosnian - Bosnia and Herzegovina (Latin)','bs-Latn-BA')
,('Breton - France','br-FR')
,('Bulgarian - Bulgaria','bg-BG')
,('Burmese - Myanmar','my-MM')
,('Catalan - Catalan','ca-ES')
,('Central Atlas Tamazight (Latin) - Algeria','fr-FR')
,('Central Atlas Tamazight (Latin) - Algeria','tzm-Latn-DZ')
,('Central Atlas Tamazight (Tifinagh) - Morocco','tzm-Tfng-MA')
,('Central Kurdish (Iraq)','ku-Arab-IQ')
,('Cherokee (Cherokee, United States)','chr-Cher-US')
,('Chinese (Simplified, China)','zh-CN')
,('Chinese - (Traditional, Taiwan)','zh-TW')
,('Corsican - France','co-FR')
,('Croatian - Bosnia and Herzegovina','hr-BA')
,('Croatian - Croatia','hr-HR')
,('Czech - Czech Republic','cs-CZ')
,('Danish - Denmark','da-DK')
,('Dari - Afghanistan','prs-AF')
,('Divehi - Maldives','dv-MV')
,('Dutch - Belgium','nl-BE')
,('Dutch - Netherlands','nl-NL')
,('Dzongkha','dz-BT')
,('English - Australia','en-AU')
,('English - Belize','en-BZ')
,('English - Canada','en-CA')
,('English - Caribbean','en-029')
,('English - India','en-IN')
,('English - Ireland','en-IE')
,('English - Jamaica','en-JM')
,('English - Malaysia','en-MY')
,('English - New Zealand','en-NZ')
,('English - Philippines','en-PH')
,('English - Singapore','en-SG')
,('English - South Africa','en-ZA')
,('English - Trinidad','en-TT')
,('English - Great Britain','en-GB')
,('English - United States','en-US')
,('English - Zimbabwe','en-ZW')
,('Estonian - Estonia','et-EE')
,('Faroese - Faroe Islands','fo-FO')
,('Filipino - Philippines','fil-PH')
,('Finnish - Finland','fi-FI')
,('French - Belgium','fr-BE')
,('French - Canada','fr-CA')
,('French - France','fr-FR')
,('French - Luxembourg','fr-LU')
,('French - Monaco','fr-MC')
,('French - Switzerland','fr-CH')
,('Frisian - Netherlands','fy-NL')
,('Fulah (Latin, Senegal)','ff-Latn-SN')
,('Galician - Galician','gl-ES')
,('Georgian - Georgia','ka-GE')
,('German - Austria','de-AT')
,('German - Germany','de-DE')
,('German - Liechtenstein','de-LI')
,('German - Luxembourg','de-LU')
,('German - Switzerland','de-CH')
,('Greek - Greece','el-GR')
,('Greenlandic - Greenland','kl-GL')
,('Guarani - Paraguay','gn-PY')
,('Gujarati - India (Gujarati Script)','gu-IN')
,('Hausa (Latin) - Nigeria','ha-Latn-NG')
,('Hawaiian - United States','haw-US')
,('Hebrew - Israel','he-IL')
,('Hindi - India','hi-IN')
,('Hungarian - Hungary','hu-HU')
,('Icelandic - Iceland','is-IS')
,('Igbo - Nigeria','ig-NG')
,('Inari Sami - Finland','smn-FI')
,('Indonesian - Indonesia','id-ID')
,('Inuktitut (Latin) - Canada','iu-Latn-CA')
,('Inuktitut (Syllabics) - Canada','iu-Cans-CA')
,('Irish - Ireland','ga-IE')
,('isiXhosa / Xhosa - South Africa','xh-ZA')
,('isiZulu / Zulu - South Africa','zu-ZA')
,('Italian - Italy','it-IT')
,('Italian - Switzerland','it-CH')
,('Japanese - Japan','ja-JP')
,('Javanese (Latin) - Indonesia','jv-Latn-ID')
,('Kannada - India (Kannada Script)','kn-IN')
,('Kazakh - Kazakhstan','kk-KZ')
,('Khmer - Cambodia','km-KH')
,('Kiche - Guatemala','qut-GT')
,('Kinyarwanda - Rwanda','rw-RW')
,('Konkani - India','kok-IN')
,('Korean(Extended Wansung) - Korea','ko-KR')
,('Kyrgyz - Kyrgyzstan','ky-KG')
,('Lao - Lao PDR','lo-LA')
,('Latvian - Legacy','lv-LV')
,('Latvian - Standard','lv-LV')
,('Lithuanian - Lithuania','lt-LT')
,('Lower Sorbian - Germany','dsb-DE')
,('Lule Sami - Norway','smj-NO')
,('Lule Sami - Sweden','smj-SE')
,('Luxembourgish - Luxembourg','lb-LU')
,('Macedonian - F.Y.R.O.M','mk-MK')
,('Malay - Brunei','ms-BN')
,('Malay - Malaysia','ms-MY')
,('Malayalam - India (Malayalam Script)','ml-IN')
,('Maltese - Malta','mt-MT')
,('Maori - New Zealand','mi-NZ')
,('Mapudungun - Chile','arn-CL')
,('Marathi - India','mr-IN')
,('Mohawk - Mohawk','moh-CA')
,('Mongolian (Cyrillic) - Mongolia','mn-MN')
,('Mongolian (Mongolian) - Mongolia','mn-Mong-MN')
,('Mongolian (Traditional Mongolian - China - Legacy)','mn-Mong-CN')
,('Mongolian (Traditional Mongolian - China - Standard)','mn-Mong-CN')
,('Nko - Guinea','nqo-GN')
,('Nepali - Federal Democratic Republic of Nepal','ne-NP')
,('Northern Sami - Finland','se-FI')
,('Northern Sami - Norway','se-NO')
,('Northern Sami - Sweden','se-SE')
,('Norwegian - Norway (Bokmål)','nb-NO')
,('Norwegian - Norway (Nynorsk)','nn-NO')
,('Occitan - France','oc-FR')
,('Odia - India (Odia Script)','or-IN')
,('Pashto - Afghanistan','ps-AF')
,('Persian','fa-IR')
,('Polish - Poland','pl-PL')
,('Portuguese - Brazil','pt-BR')
,('Portuguese - Portugal','pt-PT')
,('Punjabi - India (Gurmukhi Script)','pa-IN')
,('Punjabi (Islamic Republic of Pakistan)','pa-Arab-PK')
,('Quechua - Bolivia','quz-BO')
,('Quechua - Ecuador','quz-EC')
,('Quechua - Peru','quz-PE')
,('Romanian - Romania','ro-RO')
,('Romansh - Switzerland','rm-CH')
,('Russian - Russia','ru-RU')
,('Sakha - Russia','sah-RU')
,('Sanskrit - India','sa-IN')
,('Scottish Gaelic - Great Britain','gd-GB')
,('Serbian - Bosnia and Herzegovina (Cyrillic)','sr-Cyrl-BA')
,('Serbian - Bosnia and Herzegovina (Latin)','sr-Latn-BA')
,('Serbian - Montenegro (Cyrillic)','sr-Cyrl-ME')
,('Serbian - Montenegro (Latin)','sr-Latn-ME')
,('Serbian - Serbia (Cyrillic)','sr-Cyrl-RS')
,('Serbian - Serbia (Latin)','sr-Latn-RS')
,('Serbian - Serbia and Montenegro (Former) (Cyrillic)','sr-Cyrl-CS')
,('Serbian - Serbia and Montenegro (Former) (Latin)','sr-Latn-CS')
,('Sesotho sa Leboa / Northern Sotho - South Africa','nso-ZA')
,('Setswana / Tswana - Botswana','tn-BW')
,('Setswana / Tswana - South Africa','tn-ZA')
,('Shona - Zimbabwe','sn-Latn-ZW')
,('Sindhi (Islamic Republic of Pakistan)','sd-Arab-PK')
,('Sinhala - Sri Lanka','si-LK')
,('Skolt Sami - Finland','sms-FI')
,('Slovak - Slovakia','sk-SK')
,('Slovenian - Slovenia','sl-SI')
,('Southern Sami - Norway','sma-NO')
,('Southern Sami - Sweden','sma-SE')
,('Spanish - Argentina','es-AR')
,('Spanish - Bolivarian Republic of Venezuela','es-VE')
,('Spanish - Bolivia','es-BO')
,('Spanish - Chile','es-CL')
,('Spanish - Colombia','es-CO')
,('Spanish - Costa Rica','es-CR')
,('Spanish - Dominican Republic','es-DO')
,('Spanish - Ecuador','es-EC')
,('Spanish - El Salvador','es-SV')
,('Spanish - Guatemala','es-GT')
,('Spanish - Honduras','es-HN')
,('Spanish - Latin America','es-419')
,('Spanish - Mexico','es-MX')
,('Spanish - Nicaragua','es-NI')
,('Spanish - Panama','es-PA')
,('Spanish - Paraguay','es-PY')
,('Spanish - Peru','es-PE')
,('Spanish - Commonwealth of Puerto Rico','es-PR')
,('Spanish - Spain (International Sort)','es-ES')
,('Spanish - Spain (Traditional Sort)','es-ES_tradnl')
,('Spanish - United States','es-US')
,('Spanish - Uruguay','es-UY')
,('Standard Moroccan Tamazight - Morocco','zgh-Tfng-MA')
,('Swahili - Kenya','sw-KE')
,('Swedish - Finland','sv-FI')
,('Swedish - Sweden','sv-SE')
,('Syriac - Syria','syr-SY')
,('Tajik - Tajikistan','tg-Cyrl-TJ')
,('Tamil - India','ta-IN')
,('Tamil - Sri Lanka','ta-LK')
,('Tatar - Russia (Legacy)','tt-RU')
,('Tatar - Russia (Standard)','tt-RU')
,('Telugu - India (Telugu Script)','te-IN')
,('Thai - Thailand','th-TH')
,('Tibetan - China','bo-CN')
,('Tigrinya (Eritrea)','ti-ET')
,('Tigrinya (Ethiopia)','ti-ET')
,('Turkish - Turkey','tr-TR')
,('Turkmen - Turkmenistan','tk-TM')
,('Ukrainian - Ukraine','uk-UA')
,('Upper Sorbian - Germany','hsb-DE')
,('Urdu - India','ur-IN')
,('Urdu (Islamic Republic of Pakistan)','ur-PK')
,('Uyghur - China','ug-CN')
,('Uzbek - Uzbekistan (Cyrillic)','uz-Cyrl-UZ')
,('Uzbek - Uzbekistan (Latin)','uz-Latn-UZ')
,('Valencian - Valencia','ca-ES-valencia')
,('Vietnamese - Vietnam','vi-VN')
,('Welsh - Great Britain','cy-GB')
,('Wolof - Senegal','wo-SN')
,('Yi - China','ii-CN')
DECLARE @currencyValue decimal = 200.364
SELECT [language], FORMAT (@currencyValue, 'c', [langcode]) as FormattedDate
FROM #tempConclusion
In this article, we saw different examples of functions used to change the output for different currency formats in an MS SQL database.
Note: The FORMAT function uses Common Language Runtime (CLR) and there have been noticeable performance differences between other approaches (CONVERT Function, CAST Function, etc.) showing that FORMAT is much slower.
Next Steps
For more information refer to the following links:
- SQL Server REPLACE Function
- Format SQL Server Dates with FORMAT Function
- Format numbers in SQL Server
- SQL Server FORMAT Function for Dates, Numbers and SQL Server Agent Dates
- SQL String functions in SQL Server, Oracle and PostgreSQL
- New FORMAT and CONCAT Functions in SQL Server 2012
- How to Get Current Date in SQL Server
- Date and Time Conversions Using SQL Server
- Format SQL Server Dates with FORMAT Function
- SQL Server DateTime Functions with Examples
- SQL Server SUBSTRING
- SQL Convert Date to YYYY-MM-DD
- How SQL Server handles the date format YYYY-MM-DD
- Microsoft Learn SQL FORMAT Function
- SQL Server: How to Format the Output as Currency
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
It would probably be clearer if you chose “AS Value” at the end of the examples instead of “AS date”
Good article, Daniel. Do you have a link for the MS Documentation for this type of thing? The documentation included in the SQL Server documentation is sorely wanting.