The FORMAT() function was introduced in SQL Server 2012 as an additional way to more easily format data types. This handles multiple data types, but perhaps is most useful for dates. In this short article, we will look at some of the ways that you can use FORMAT() to change the way that you return dates in a result set.
A Word of Warning
The FORMAT() function, while convenient and easy to use in clean code, has some performance issues. Aaron Bertrand has a great article that benchmarks FORMAT() against CONVERT with different types.
Be careful about using FORMAT() in any situation where performance is important, or large data sets are involved. There is a substantial performance hit compare to CAST/CONVERT.
Syntax
The basic syntax for FORMAT() is as follows:
FORMAT( value, format, culture).
Of these, the value and format are required. The culture is optional. The references include links, but the .NET string formats and the cultures have a set of values that I'd say are non-intuitive. Most of you may know your own culture/language code, but do you know others? There are quite a few.
The value must be a numeric or a date/time value. Strings are not supported here. This means that the int variants, the float variants and money are OK. The various date, time, and datetime types are supported as well.
The formats essentially break down into long and short dates, full dates, general dates, short times, universyal, and more. There are single character code as well as longer strings that you can use to represent dates.
Cultures correspond to the language and culture codes. These are codes that are used should be valid codes. The BOL documentation says that these must be valid cultures, and that any culture supported by SQL Server or .NET can be used. If culture is not provided, the default for the session is used. The same seems to be true if the format is slightly off, but not always. As we'll see, there are cases where errors are returned and cases where they are not.
One thing to keep in mind is that FORMAT() returns an NVARCHAR() string. There are a couple implicaitons of this. First, you are returning a string, and if you expect a date or number, you will need an implicit or explicit conversion. Second, many US based systems work with varchar() or non-Unicode strings. If you pass this further along to other code, you might create implicit conversions, which can be a source of performance issues.
Usage
FORMAT() is a function like many other T-SQL functions and can be used in any place that a function is used. As with most functions, I'd be wary of using this in an ON or WHERE clause as you'll reduce performance and perhaps prevent an index from being used.
Let's look at a simple example. If I have dates stored in a variable, I can use FORMAT() to ensure that I get the result I want. If I want to format the current time as a US date, I can do this on my system with any ofthe following code:
DECLARE @dt DATETIME2 = SYSDATETIME(); SELECT FORMAT(@dt, 'd') AS defaultformat
That returns this value on my US default instance:
defaultformat ------------- 4/11/2018
Actually, it returns a much longer string of dashes SSMS seems to think this is a NVARCHAR(250) or whatever the max result is for your configuration. This despite a LEN() of that result returns 9.
This is the short date format, which is the month, day, and year of the input. If I wanted the long date, I'd use a "D" for the format, which would return:
defaultformat -------------------------- Wednesday, April 11, 2018
Let's now look at the various ways in which we can format things. For ease of viewing, I've included the different formats on different lines. All of these formats are US English.
DECLARE @dt DATETIME2 = SYSDATETIME();
SELECT 'Default Format' ,
FORMAT(@dt, 'd')
UNION
SELECT 'Long Date' ,
FORMAT(@dt, 'D')
UNION
SELECT 'Full Date Short Time' ,
FORMAT(@dt, 'f', 'us-en')
UNION
SELECT 'Full Date Long Time' ,
FORMAT(@dt, 'F', 'us-en')
UNION
SELECT 'General Date Short Time' ,
FORMAT(@dt, 'g', 'us-en')
UNION
SELECT 'General Date Long Time' ,
FORMAT(@dt, 'G', 'us-en')
UNION
SELECT 'Univeral Date Short Time' ,
FORMAT(@dt, 'u', 'us-en')
UNION
SELECT 'Sortable Date Short Time' ,
FORMAT(@dt, 's', 'us-en')
UNION
SELECT 'RFC 1123 r Date Time' ,
FORMAT(@dt, 'r', 'us-en')
UNION
SELECT 'Short Time' ,
FORMAT(@dt, 't', 'us-en')
UNION
SELECT 'Long Time' ,
FORMAT(@dt, 'T', 'us-en')
UNION
SELECT 'Universal Date Long Time' ,
FORMAT(@dt, 'U', 'us-en');
GOThe results here let me compare the various formats.
---------------------- ---------------------------------- Default Format 4/11/2018 Full Date Long Time Wednesday, 11 April 2018 11:59:23 Full Date Short Time Wednesday, 11 April 2018 11:59 General Date Long Time 04/11/2018 11:59:23 General Date Short Time 04/11/2018 11:59 Long Date Wednesday, April 11, 2018 Long Time 11:59:23 RFC 1123 r Date Time Wed, 11 Apr 2018 11:59:23 GMT Short Time 11:59 Sortable Date Short Time 2018-04-11T11:59:23 Univeral Date Short Time 2018-04-11 11:59:23Z Universal Date Long Time Wednesday, 11 April 2018 17:59:23
As you can see, I get a series of formats that would likely handle any requirements that I had. A few things to point out. I can get date or time, or both, with the day of the week included.
All of these have the culture of US English (en-us). If I change that to Great Britain, I get this:
------------------------ ----------------------------- Default Format 11/04/2018 Full Date Long Time 11 April 2018 12:04:37 Full Date Short Time 11 April 2018 12:04 General Date Long Time 11/04/2018 12:04:37 General Date Short Time 11/04/2018 12:04 Long Date 11 April 2018 Long Time 12:04:37 RFC 1123 r Date Time Wed, 11 Apr 2018 12:04:37 GMT Short Time 12:04 Sortable Date Short Time 2018-04-11T12:04:37 Univeral Date Short Time 2018-04-11 12:04:37Z Universal Date Long Time 11 April 2018 18:04:37
These make sense to everyone at Redgate in the UK, but I'd read many of the shorter formats as Nov 4 instead of April 11.
In Japanese (jp-JP), I'd get this:
---------------------- ----------------------------------- Default Format 04/11/2018 Full Date Long Time Wednesday, 11 April 2018 12:07:24 Full Date Short Time Wednesday, 11 April 2018 12:07 General Date Long Time 04/11/2018 12:07:24 General Date Short Time 04/11/2018 12:07 Long Date Wednesday, 11 April 2018 Long Time 12:07:24 RFC 1123 r Date Time Wed, 11 Apr 2018 12:07:24 GMT Short Time 12:07 Sortable Date Short Time 2018-04-11T12:07:24 Univeral Date Short Time 2018-04-11 12:07:24Z Universal Date Long Time Wednesday, 11 April 2018 18:07:24
Culture clearly affects the results. Since I can use a variable here, I can programmatically change things, like this:
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'zh-CN';
SELECT 'Long Date Format' ,
FORMAT(@dt, 'D', @culture)
This gives me these results:
---------------- ------------- Long Date Format 2018?4?11?
Clearly culture affects the pre-coded formats, but there are other choices. I can use various items in the format string to control the display. For example, I can always ensure a day, month, year format with:
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'en-US';
SELECT 'Custom Format' ,
FORMAT(@dt, 'dd MM yyyy', @culture)
------------- ------------
Custom Format 11 04 2018
This is helpful, but I want to use my own seperators, such as:
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'en-US';
SELECT 'Custom Format' ,
FORMAT(@dt, 'dd of MM, yyyy', @culture)
------------- ------------
Custom Format 11 o4 04, 2018
What happened here? I have the "o" in "of", but then a 4. The f is a token that returns different values. You can read about f here, but this is really the significant digit of the seconds fraction. Not what I want. I can escape this out with a backslash, like this:
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'en-US';
SELECT 'Custom Format' ,
FORMAT(@dt, 'dd o\f MM, yyyy', @culture)
------------- ------------
Custom Format 11 of 04, 2018
I can do whatever I want here with formatting, as long as I escape out tokens, colons, and periods.
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'en-US';
SELECT 'Custom Format' ,
FORMAT(@dt, 'dd \d\a\y \o\f MMM, yyyy', @culture)
------------- -------------------
Custom Format 11 day of Apr, 2018
Note that I get the month name if I use 3 Ms. If I go to 4, I get the full name.
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'en-US';
SELECT 'Custom Format' ,
FORMAT(@dt, 'dd \d\a\y \o\f MMMM, yyyy', @culture)
------------- ---------------------
Custom Format 11 day of April, 2018
If I want the day of the week, I can get that as well.
DECLARE @dt DATETIME2 = SYSDATETIME()
, @culture VARCHAR(10) = 'en-US';
SELECT 'Custom Format' ,
FORMAT(@dt, 'dd \d\a\y \o\f MMMM, yyyy, a dddd', @culture)
------------- ----------------------------------
Custom Format 11 day of April, 2018, a Wednesday
There are plenty of custom strings that you can use for the various date and time parts. If you want to add in other verbiage, then you need to ensure that any letters that might need escaping have a backslash in front of them.
Conclusion
FORMAT() is a handy function, and allows access to the equivalent .NET functions from T-SQL. This can be useful when you want to ensure that dates are returned to a client in a specific fashion. You might use this with information about a client's regional settings to format data in reports to match their expectations.
However, as noted in the warning section above, FORMAT() rakes more resources to implement and can cause performance problems at larger scales or users or data sets. Use this carefully in your applications. If you are doing any work that might apply at scale, be careful of this function.
References
- Format - https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql
- .NET String Formats - https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings
- .NET Cultures - https://msdn.microsoft.com/en-us/library/ee825488%28v=cs.20%29.aspx?f=255&MSPPError=-2147217396
- Custom Date and Time Format Strings
