SQL Server : Format Function for DATE/TIME Data Type

We've talked about formating number/numeric data types. Now let's see how the FORMAT function works for the DATE/TIME data type.

Syntax:

FORMAT( value, format [, culture ] )

value : value that will be formatted
format: format pattern in NVARCHAR / String 

culture: this is optional, regarding regional settings

The format date samples in some cultures:

DECLARE @d DATE = GETDATE();
SELECT 'US English' AS Cultures,   FORMAT( @d, 'd', 'en-US' ) AS Formatted UNION ALL
SELECT 'Great Britain English',    FORMAT( @d, 'd', 'en-gb' ) UNION ALL
SELECT 'German',                   FORMAT( @d, 'd', 'de-de' ) UNION ALL
SELECT 'Chinese Simplified (PRC)', FORMAT( @d, 'd', 'zh-cn' ) UNION ALL
SELECT 'US English',               FORMAT( @d, 'D', 'en-US' ) UNION ALL
SELECT 'Great Britain English',    FORMAT( @d, 'D', 'en-gb' ) UNION ALL
SELECT 'German',                   FORMAT( @d, 'D', 'de-de' ) UNION ALL
SELECT 'Chinese Simplified (PRC)', FORMAT( @d, 'D', 'zh-cn' )

Date format in the custom formatting:

DECLARE @d DATE = GETDATE();  
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS 'Date 1', 
       FORMAT( @d, 'MM/dd/yyyy', 'en-US' ) AS 'Date 2',
       FORMAT( @d, 'yyyy-MM-dd', 'en-US' ) AS 'Date 3'