SQL 2012 FORMAT for Date formatting

In SQL Server 2008 and earlier version used the CONVERT() function to handle the date formatting. As we notice that the function is not very user friendly.

In SQL Server 2012, a new function named FORMAT() has been introduced which is much easier to use.

First of all we have look about CONVERT() function and then we are going to new SQL Server 2012 FORMAT() function.

Some example of CONVERT()  function is mentioned bellow

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)                                        -- Oct  2 2010 11:01AM         

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010                 

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02          

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        -- Oct  2 2010 11:02:44:013AM   

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

-- yyyymmdd - ISO date format - international standard - works with any language setting

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

                                        -- 02 Oct 2010 11:02:07:577    

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm - ISO8601

                                        -- 2010-10-02T10:52:47.513

SELECT convert(varchar, getdate(), 127) -- yyyy-mm-ddThh:mm:ss.mmmZ - with time zone

SELECT convert(nvarchar(64), getdate(), 130) -- Arabic Hijri date, 15 جمادى الثانية 1433  2:40:09:210PM

SELECT convert(nvarchar, getdate(), 131) -- Arabic Hijri date-Islamic calendar, 15/06/1433  2:40:39:240PM

-- Without century (YY) date / datetime conversion - there are exceptions!

SELECT convert(varchar, getdate(), 0)   -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(varchar, getdate(), 1)   -- mm/dd/yy

SELECT convert(varchar, getdate(), 2)   -- yy.mm.dd         

SELECT convert(varchar, getdate(), 3)   -- dd/mm/yy

SELECT convert(varchar, getdate(), 4)   -- dd.mm.yy

SELECT convert(varchar, getdate(), 5)   -- dd-mm-yy

SELECT convert(varchar, getdate(), 6)   -- dd mon yy

SELECT convert(varchar, getdate(), 7)   -- mon dd, yy

SELECT convert(varchar, getdate(), 8)   -- hh:mm:ss

SELECT convert(varchar, getdate(), 9)   -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 10)  -- mm-dd-yy

SELECT convert(varchar, getdate(), 11)  -- yy/mm/dd

SELECT convert(varchar, getdate(), 12)  -- yymmdd

SELECT convert(varchar, getdate(), 13)  -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 14)  -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 20)  -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 21)  -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 22)  -- mm/dd/yy hh:mm:ss AM (or PM)

SELECT convert(varchar, getdate(), 23)  -- yyyy-mm-dd

SELECT convert(varchar, getdate(), 24)  -- hh:mm:ss

SELECT convert(varchar, getdate(), 25)  -- yyyy-mm-dd hh:mm:ss.mmm

-- SQL create different date styles with t-sql string functions

SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd

SELECT convert(varchar(7), getdate(), 126)                 -- yyyy-mm

SELECT right(convert(varchar, getdate(), 106), 8)          -- mon yyyy

SELECT substring(convert(varchar, getdate(), 120),6, 11)   -- mm-dd hh:mm

In SQL Server 2012 the FORMAT function

In SQL Server 2012 introduced a new function named FORMAT to handle formatting dates. It is similar to ORACLE to_date() function.

The syntax of the FORMAT() function is mentioned bellow.

FORMAT(value , format [,culture])

Example of FORMAT()

SELECT FORMAT (getdate(), 'dd-MM-yy') as date -- 05-06-12

SELECT FORMAT (getdate(), 'hh:mm:ss') as time -- 2:48:42

With Culture Option

Culture is the another option of the FORMAT() function. By this we can obtain the regional formatting.

Example of USA format

SELECT FORMAT (getdate(), 'd', 'en-us') as date -- 6/5/2012

Example of Spanish culture in Bolivia

SELECT FORMAT (getdate(), 'd', 'es-bo') as date -- 5/6/2012

More Examples

Query	Sample output
select FORMAT (getdate(), 'dd/MM/yyyy ') as date	06/05/2012
select FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date	06/05/2012, 11:33:13
select FORMAT (getdate(), 'dddd, MMMM, yyyy') as date	Wednesday, May, 2012
select FORMAT (getdate(), 'MMM dd yyyy') as date	May 06 2012
select FORMAT (getdate(), 'MM.dd.yy') as date	05.06.12
select FORMAT (getdate(), 'MM-dd-yy') as date	05-06-12
select FORMAT (getdate(), 'hh:mm:ss tt') as date	11:36:14 AM
select FORMAT (getdate(), 'd','af') as date	2012/05/06

Hope you like it.

Posted by: MR. JOYDEEP DAS