T-SQL Tips: Fun with date FORMATing

FORMAT was a new function introduced in SQL 2012 which can be use to format date,numeric values to a variety of different formats and styles.
This blog discusses on some of the cool date value formatting tips that can be done using the FORMAT function.

1. Display the date value based on a language/locale culture

declare @dt datetime = GETDATE()

SELECT FORMAT(@dt,'d','en-GB') AS BritishFormat,FORMAT(@dt,'D','en-US') AS UsFormat,
FORMAT(@dt,'D','zh-cn') AS ChineseFormat,FORMAT(@dt,'D','ta-IN') AS TamilIndiaFormat,
FORMAT(@dt,'D','ml-IN') AS MalayalamIndiaFormat

2. Display date as per any of the custom formats

declare @dt datetime = GETDATE()

SELECT FORMAT(@dt,'dd/MM/yyyy hh:mm:ss tt') AS DateWithTime12h,

FORMAT(@dt,'dd MMM yyyy HH:mm:ss') AS DateWithTime24h,

FORMAT(@dt,'HH:mm:ss.fff') AS TimeAloneWithMillisec,

FORMAT(@dt,'yyyy-MM-dd HH:mm:ss.fff zz') AS DateTimeWithOffset,

FORMAT(@dt,'dddd dd MMMM yyyy gg') AS DayNameWithDateEra

3. Display component parts of a date value based on custom formats

declare @dt datetime = GETDATE()

SELECT FORMAT(@dt,'yyyy\MM') AS Period,

FORMAT(@dt,'hh:mm:ss tt') AS Time12h,

FORMAT(@dt,'HH:mm:ss') AS Time24h,

FORMAT(@dt,'dddd dd\t\h MMMM') AS DayMonthDate,

FORMAT(@dt,'HH \h mm \min ss \sec') AS TimeElapsedSinceDayStart,

FORMAT(@dt,'yyyy-MM-ddTHH:mm:sszzz') AS XMLDateFormat,

FORMAT(@dt,'yyyy-MM-ddTHH:mm:ssZ') AS XMLDateFormatUTC

From the above we can see that FORMAT is a very useful function which provides the ability of generating flexible presentation formats from a given date value.