SQL SERVER – Learning New Multipurpose FORMAT Function

In versions prior to 2012, you need to use the CONVERT function with different styles to convert the date values into a different format. Let us learn about the FORMAT function in this blog post.

Let us create this simple dataset

CREATE TABLE #date_test(dates DATETIME)
INSERT INTO #date_test(dates)
SELECT '2016-10-19 10:22:34' AS DateTest

Executing above query will return no result, but it will create a temporary table with one row.

Suppose you want to format the dates in either dd/mm/yyyy or mm/dd/yyyy format. You can do it by

SELECT dates,CONVERT(VARCHAR(10),dates,101) AS [dates_mm/dd//yyyy] 
FROM #date_test
SELECT dates,CONVERT(VARCHAR(10),dates,103) AS [dates_dd/mm/yyyy] 
FROM #date_test

Executing above query will return following result:

If you want to show only month name, you can use

SELECT DATENAME(month,dates) AS [month_name]
FROM #date_test

Executing above query will return following result:

If you want to show only day name, you can use

SELECT DATENAME(weekday,dates) AS [day_name] 
FROM #date_test

Executing above query will return following result:

As you can see, you need to use either CONVERT or different functions based on what you want to do. This requires the usage of many different functions.

But with version 2012 it is very easy. All you need to do is just make use of FORMAT function

SELECT dates,FORMAT(dates,'dd/MM/yyyy') AS [dates_dd/mm/yyyy]
FROM #date_test
SELECT dates,FORMAT(dates,'MM/dd/yyyy') AS [dates_mm/dd/yyyy]
FROM #date_test

Executing above query will return following result:

Just use change the second parameter accordingly

To display month name, or day name

SELECT dates,FORMAT(dates,'MMMM') AS [month_name]
FROM #date_test
SELECT dates,FORMAT(dates,'dddd') AS [day_name] 
FROM #date_test

Executing above query will return following result:

You can also display long formatted dates with customized text included

SELECT dates,FORMAT(dates,'"The given date is " MMMM d, yyyy') AS long_format 
FROM #date_test

Executing above query will return following result:

Now let us run following script

SELECT dates,FORMAT(dates,'"This is generated on" M/d/y "at" hh:mm:ss') AS long_format 
FROM #date_test

Executing above query will return following result:

Not only you can do date time formatting, you can also do number formatting using the FORMAT function.

Suppose you want to always make 6 digits number and if the length is less than 6, you want to prefix leading zeroes to make the total length as 6, you can do it by

SELECT 935 AS [number], FORMAT(935,'000000') AS length_6

Executing above query will return following result:

I believe this new function FORMAT is very handy function and after using this, you may not have to remember many different functions to the same task.

Reference: Pinal Dave (https://blog.sqlauthority.com)