Microsoft Business Intelligence: Format Function Sql Server

Friday, 9 December 2016

Format Function Sql Server

Suppose we want to convert date, Currency or Number with Culture, We used the convert function to do this in SQL Server 2008 and older version of SQL Server but in 2012 a new function introduced that is format function & it’s very useful to format the date and integer value.

Format () is one of the new built-in String Function introduced as a Part of Sql Server 2012. It returns the value formatted in the specified format using the optional culture parameter value. It is not a Sql Server native function instead it is .NET CLR dependent function.

Syntax

Parameter	Description
Value	Value to be formatted (Date, Currency or Number)
Format	This parameter specifies the format in which the value will be formatted.
Culture	This parameter is optional. It specifies the culture in which the value is formatted. If it is not specified then the language of the current session is used.( US,Hind,tamil,UK etc)

Note: It will return the nvarchar.

In other word we can say that the function FORMAT () accepts 3 parameters. The first parameter is the VALUE parameter where you pass the date value or numeric value. The second parameter is the.NET Framework format string. The format parameter is case sensitive. "D" doesn’t mean the same as "d". The third parameter is the culture. This can be any culture supported by the .NET Framework.

Culture info read here :

https://msdn.microsoft.com/en-us/library/system.globalization.cultureinfo(v=vs.80).aspx

See the Example

Date Formatting

DECLARE@dateDATETIME=GETDATE()

--Weekday date name, month name and the day with year

SELECTFORMAT(@date,'D','en-US')AS[Full Date];

SELECTFORMAT(@date,'d','en-US')AS[Month/Day/Year];

--Display only the month name and date

SELECTFORMAT(@date,'m','en-US')AS[Moanth and Date];

--Display long date and time

--"tt" stands for two-letter representation of AM/PM and the result will be "AM" or "PM"

SELECTFORMAT(@date,'yyyy/MM/ddhh:mm:sstt','en-US')AS[Long Date and Time];

--Display the millionths of a second

SELECTFORMAT(@date,'h\:m\:ss\.ffffff','en-US')AS[Time with millionths os second];

See the Output

We can display the date and time with Culture. See below example

DECLARE@dateDATETIME=GETUTCDATE()

SELECT@dateAS'UTCDate',

FORMAT(@date,'D','en-US')AS[US Culture Date],

FORMAT(@date,'D','en-IN')AS[Indian Culture Date],

FORMAT(@date,'D','hi-IN')AS[Date in Hindi],

FORMAT(@date,'D','ta-IN')AS[Date in Tamil]

SELECT@dateAS'UTCDate',

FORMAT(@date,'D','zh-CN')AS[Date in China],

FORMAT(@date,'D','de')AS[Date in German],

FORMAT(@date,'D','ur')AS[Date in Urdu],

FORMAT(@date,'D','mr-IN')AS[Date in Marathi]

See the Output

Formatting with the NUMBERS

DECLARE@moneymoney='2500';

SELECTFORMAT(@money,'C')AS[Money]

See the output

Here, we are getting the currency symbol ‘$’ because my current locale language setting is en-us. I could also display the currency ‘$’ explicitly by using the culture parameter as shown below.