DateTime Format in SQL Server 2012 with New Function FORMAT()


Here comes the new function FORMAT in SQL Server 2012. It can format different data types to a string type defined. The basic syntax is: FORMAT ( value, format [, culture ] ) .

This function makes the format datetime job a lot easier compared with how it was done in previous SQL Server versions with CONVERT function.  You can pass the a datetime/time value to the function along with a format (standard or custom .net format string), you will get your expected result. Here are  a few examples with my default culture:

SELECT FORMAT(GETDATE(), ‘d’)

/* 3/14/2012 */

 

SELECT FORMAT(GETDATE(), ‘G’)

/* 3/14/2012 4:39:52 PM */

 

SELECT FORMAT(GETDATE(), ‘t’)

/* 3/14/2012 */

 

SELECT FORMAT(GETDATE(), ‘MMM-dd-yyyy’)

/* Mar-14-2012 */

 

SELECT FORMAT(GETDATE(), ‘MM-dd-yyyy’)

/*03-14-2012 */

 

SELECT FORMAT(GETDATE(), ‘MMMM dd, yyyy’)

/*March 14, 2012 */

For a full list of standard and some custom formats, you can refer to these links from MSDN:

http://msdn.microsoft.com/en-us/library/az4se3k1.aspx

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

You can always check bookOnline for the FORMAT function if you want to know more:

http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx

 

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s