Use of Format() function in SQL Server

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.

Syntax: FORMAT ( value, format [, culture ] )

Formatting the DATE

Let us see some examples with DATE as  the value by changing the format.

Example 1:

To display weekday date name, month name and the day with year, you could use the following:

DECLARE @date DATETIME = '12/21/2011';

SELECT FORMAT ( @date, 'D', 'en-US' ) AS FormattedDate;

Result:

Wednesday, December 21, 2011

Example 2:

To display only the month name and day, you could use the following example.

DECLARE @date DATETIME = '12/21/2011';

SELECT FORMAT ( @date, 'm', 'en-US' ) AS FormattedDate;

Result:

December 21

Example 3

To display long date and time use the following example.

DECLARE @date DATETIME = getdate();

SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ) AS FormattedDate;

Result:

2011/12/21 11:49:00 PM

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

If you use a single "t" the "t"  stands for the one-letter representation of AM/PM and the result will be "A" or "P" representing the AM and PM respectively.

Example:

DECLARE

@date DATETIME = convert(datetime,'2011/01/01 2:00:00');

SELECT

FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss t','en-US' ) AS FormattedDate;

Result

FormattedDate

2011/01/01 02:00:00 A

DECLARE

@date DATETIME = convert(datetime,'2011/01/01 14:00:00');

SELECT

FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss t','en-US' ) AS FormattedDate;

Result

FormattedDate

2011/01/01 02:00:00 P

DECLARE

@date DATETIME = convert(datetime,'2011/01/01 2:00:00');

SELECT

FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt','en-US' ) AS FormattedDate;

Result

FormattedDate

2011/01/01 02:00:00 AM

DECLARE

@date DATETIME = convert(datetime,'2011/01/01 14:00:00');

SELECT

FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt','en-US' ) AS FormattedDate;

Result

FormattedDate

2011/01/01 02:00:00 PM

Example 4:

The following example displays the time.

DECLARE @date DATETIME = getdate();

SELECT FORMAT ( @date, 'h\:m\:ss\.ffffff', 'en-US' ) AS FormattedDate;

Result:

12:10:15.453000

Note: ffffff represents the millionths of a second.

For more on Format function go through given link: Format() function in SQL server