SQL 2012 FORMAT for Date formatting

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

Related Posts