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.
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,’yyyyMM’) AS Period,
FORMAT(@dt,’hh:mm:ss tt’) AS Time12h,
FORMAT(@dt,’HH:mm:ss’) AS Time24h,
FORMAT(@dt,’dddd ddth 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.
