T-SQL formatting Dates and Times using FORMAT

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Love to Help
Give a man a fish and you've fed him for a day; Teach a man to fish and you've fed him for the rest of his life. Be the teacher
Published:
Updated:
By Mark Wills

Formatting dates and times has aways presented a few challenges in T-SQL. Seems most other languages support a FORMAT command.

Well, time to rejoice because (finally) MS SQL 2012 (and more recent) now supports the FORMAT function. One little "gotcha" is you need .Net Framework because it leverages CLR. Which also means that it cannot be remoted (which is nothing new for CLR runtime).

So, with that out of the way, and for a large majority of T-SQL requirements, it is like any other system function. Probably easiest to show you :)

First, some dummy data. Create an order table with date and price columns, then populate :-
CREATE TABLE EE_tbl_Orders (CUSTOMER varchar(20),ORDER_DATE datetime, ORDER_PRICE decimal(10,4));


INSERT INTO EE_tbl_Orders (CUSTOMER, ORDER_DATE, ORDER_PRICE) VALUES ('Fred','2015-08-20 20:30:40',1234.5678);

Open in new window


The structre of the command is FORMAT ( value, format_str [], culture ] ) and can be found at MSDN Now, [culture] is optional and I tend to leave it out so it can default to the way my server / systems are set up.

So, with our Order Table, let us experiment with our new command...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), Order_Price
FROM EE_tbl_Orders


-- Results

-- Fred      2015 Aug 20      1234.5678

Open in new window


Now, we can also do something with that price as well (and note the rounding)...

SELECT Customer, FORMAT(Order_Date,'yyyy MMM dd'), FORMAT(Order_Price,'C')
FROM EE_tbl_Orders
 
-- Results
-- Fred      2015 Aug 20      $1,234.57

Open in new window


Makes it a LOT easier, especially if you are familiar with format codes from other languages. Basically, the format characters in the format_str is one of d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" and known as a format specifier. Any other character will show as is. If you want the result to contain one of those characters, you have to use the backslash "\" as an escape character (including for its own use where you want a backslash).

For example date delimiters... Including a hyphen just needs to be there, but a forward slash needs to be 'escaped' with a backslash first :

SELECT Customer, FORMAT(Order_Date,'yyyy-MMM-dd'), FORMAT(Order_Date,'yyyy\/MMM\/dd'), FORMAT(Order_Price,'C')
FROM EE_tbl_Orders
 
-- Results
-- Fred      2015-Aug-20      2015/Aug/20      $1,234.57

Open in new window



There is now a significant function (finally) available to us T-SQL programmers. We can get pretty sophisticated such as FORMAT(Order_Date,'"Ordered On "yyyy-MMM-dd') including a double quoted string. But that is probably another Article.

Well, hope I have been able to share something new in T-SQL 2012. Please vote "Good Article" below.

 

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Love to Help
Give a man a fish and you've fed him for a day; Teach a man to fish and you've fed him for the rest of his life. Be the teacher
8
2,173 Views

Comments (1)

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Darn handy, especially for those that are stuck supporting 2008R2 databases.  Voted Yes.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
Continue Growing Your Skills and Your Career
  • Interact with leading experts on your specific technology problems.
  • Receive the guidance of experienced professionals.
  • Learn from troubleshooting others have experienced.
  • Gain knowledge from a library of courses, all included.