Home » SQL Server Format Function

SQL Server Format Function

The  SQL Format function is a SQL Server string function which is used to format the specified value in the given format such as to format the date/time values and number values.





It is used for locale-aware formatting of date/time and number values as strings

FORMAT function returns NULL, if the value specified in format is not valid.

The return type of FORMAT function is nvarchar or null

SYNTAX

FORMAT ( value, format [, culture ] )

Value – The value to be formatted.
Format – The specified format in which value will be formatted.
Culture – The Culture is optional. If the culture argument is not provided then the language of the current session is used by SQL Server.

Lets look at an example of FORMAT In SQL Server.

FORMAT DATE USING CULTURES

Following statement uses Format function and returns a formatted date in different cultures.

DECLARE @date datetime = GETDATE();
SELECT
FORMAT(@date, ‘d’, ‘en-us’) ‘US’,
FORMAT(@date, ‘d’, ‘hi-in’) ‘India’,
FORMAT(@date, ‘d’, ‘de-DE’) ‘Germany’,
FORMAT(@date, ‘d’, ‘fr-FR’) France‘,
FORMAT(@date, ‘d’, ‘zh-cn’) ‘China’

DECLARE @date datetime = GETDATE();
SELECT
FORMAT(@date, ‘D’, ‘en-us’) ‘US’,
FORMAT(@date, ‘D’, ‘hi-in’) ‘India’,
FORMAT(@date, ‘D’, ‘de-DE’) ‘Germany’,
FORMAT(@date, ‘D’, ‘fr-FR’) France‘,
FORMAT(@date, ‘D’, ‘zh-cn’) ‘China’

 





FORMAT DATE USING CUSTOM FORMAT

Following Statement uses custom format to display date time.

DECLARE @date DATETIME = GETDATE() 
SELECT 'date in d/m/yy' as Particular, FORMAT(@date, 'd/m/yy') AS Format UNION
SELECT 'date in mm/dd/yyyy' ,FORMAT(@date, 'mm/dd/yyyy') UNION
SELECT 'date in dd/mm/yyyy', FORMAT(@date, 'dd/mm/yyyy') UNION
SELECT 'date in dd/mm/yy' , FORMAT(@date, 'dd/mm/yy') UNION
SELECT 'date in MMMM dddd yyyy hh:mm:ss:mmmm', 
FORMAT(@date, 'MMMM dddd yyyy hh:mm:ss:mmmm') UNION
SELECT 'date in MMMM yyyy, dddd hh:mm:ss:mmmm', 
FORMAT(@date, 'MMMM yyyy, dddd hh:mm:ss:mmmm')

FORMAT TO DISPLAY CURRENCY FOR CULTURES

Following statement uses format to display currency for different different cultures

DECLARE @amount money = 584020.89;
SELECT @amount as Amount ,
FORMAT(@amount, 'C', 'th-TH') 'Thailand',
FORMAT(@amount, 'C', 'de-DE') 'Germany',
FORMAT(@amount, 'C', 'en-gb') 'British',
FORMAT(@amount, 'C', 'en-us') 'US',
FORMAT(@amount, 'C', 'en-in') 'India',
FORMAT(@amount, 'C', 'fr-FR') 'France',
FORMAT(@amount, 'C', 'zh-cn') 'China'

 

 




Also Read

UPPER()

LOWER()

TRIM()

LRTIM()

RTIM()

SPACE()

TRANSLATE()

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 927 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.