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)

File format, SQL DateTime, SQL Function, SQL Scripts, SQL Server
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

3 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
  • Heh… 5 years and no answer to the performance question that Archana posted.

    Yes. You’re performance issue is because you used FORMAT. Behind the scenes (not including output to the screen or disk), it’s at least 23 times slower than even some of the more complicated CONVERT code you might come up with. I don’t use the word very often but I NEVER use FORMAT no matter how complicated an output I may require. Saving a couple of minutes in programming time just isn’t worth the horrible performance that your code will suffer every day.

    Reply

Leave a ReplyCancel reply

Exit mobile version