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.

SQL SERVER - Learning New Multipurpose FORMAT Function format0-800x427

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format1

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format2

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format3

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format4

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format5

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format6

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format7

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:

SQL SERVER - Learning New Multipurpose FORMAT Function format8

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)

, , , ,
Previous Post
SQL SERVER – Not Able to Kill SPID with KILL WITH STATUSONLY
Next Post
SQL Server – How to Get Column Names From a Specific Table?

Related Posts

2 Comments. Leave new

  • Thomas Franz
    June 23, 2017 3:53 pm

    you should mention, that FORMAT() is much slower than CONVERT() or simalar “real internal” functions, so it should not be used for big datasets when performance matters.

    Reply
  • I used the format function. But it is slowing down the sp I am using it in.
    It returns about 3000 rows with format() date funstion.
    Is it becoz of this format() date func. ?

    Reply

Leave a Reply

Menu