How to Format Dates in SQL Server (Hint: Don’t Use FORMAT!) – SQLServerCentral (2023)


First of all, I agree with most of the heavy hitters in saying that you should avoid formatting in both the “Data Layer” and the “Business Layer” of your applications and that it should only be done in the “Presentation Layer”. The two big reasons for that are…

  1. It’s cheaper to do such conversions in the application layer. SQL Server is expensive compared to the application layer and shouldn’t usually be bogged down with formatting tasks.
  2. If done properly in the “Presentation Layer”, the dates and times can follow the “local formatting” that the user has on their computer.

Still, there are occasions where the “Data Layer” and the “Presentation Layer” are the same. As just one example, if you have to send out files that contain dates as strings, then you frequently have to do the formatting in the “Data Layer” to correctly populate the file.

In such cases, you want the formatting to be as fast as possible for both duration and CPU usage, and that’s a nice segue into a rather nasty problem.

The FORMAT() function, which can be used to format dates (and other things) as strings, made its appearance in SQL Server 2012 which, at the time of this writing, was almost a decade ago. It was known even before it came out that it has a terrible performance issue and yet people keep writing about how to use it without that warning.

Others have written about the performance problem and do tests that demonstrate that it’s 3 to 4 times slower than using the CONVERT() function. That’s actually a gross understatement. If you take out display and disk times, it’s actually much worse and we’re going to prove it in this article

While the FORMAT() function is convenient, especially for those well versed in .NET, the purpose of this article is to convince you that it should NEVER be used in SQL Server… and “NEVER” is a word that I don’t use often!

I’ll also tell you that we're not going to teach you everything about formatting strings in this article. A short book could be written on the subject and that’s not the objective of this article. The objective of this article is to convince people that you shouldn’t use the FORMAT() function when you need to format strings, especially but not limited to Dates and Times.

(Video) An Intro to SQL Source Control with Steve Jones | Redgate

Setting Up the Test Table

The test table consists of a million rows in a typical configuration with an ID column, a column that contains a DATETIME, and a few other columns. We’ll simulate those several other columns using a single “Fluff” column that’s always 130 bytes wide by using a fixed width CHAR(130) as its datatype.

Also, I use my fnTally() function as a high performance row source to replace various forms of RBAR1 , which includes WHILE loops, recursive CTEs, recursive functions, and “GO nnn”. You can, of course, use your own if you have one. If you don’t have one, you can get a copy of mine at the following URL:

Here’s the code to build the test table as a temporary table in TempDB. On my box, it takes less than a second to complete. The first 1,000 rows will be displayed once the table is created.

--=====================================================================================-- Build a test table containing 1 million rows of randomized dates and times.--=====================================================================================--===== Conditionally Drop the Test Table =============================================DROPTABLEIFEXISTS#FormatTest;GO--===== Create the Test Table =========================================================SETNOCOUNTOFF;CREATETABLE#FormatTest(RowNumINTIDENTITY(1,1)PRIMARYKEYCLUSTERED,SomeDTDATETIME--OurrandomDATETIMEcolumn,FluffCHAR(130)DEFAULT'X'--Simulatedseveralothercolumns);--===== Populate the Test Table =======================================================--Onemillionrowsoftestdataspreadacross10yearsofdatesDECLARE@LoDTDATETIME='2020'--InclusiveStartDate,@CoDTDATETIME='2030'--ExlusiveCutOffDate;INSERTINTO#FormatTestWITH(TABLOCK)--RequiredforMinimalLogging(SomeDT)SELECTSomeDT=RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,@LoDT,@CoDT)+@LoDTFROMdbo.fnTally(1,1000000);--===== Let's see a sample of what the table contains... ==============================SELECTTOP1000*FROM#FormatTestORDERBYRowNum;

Test Code Structure

Each test will have the following nearly sections:

  1. A “Title” section to identify the test being conducted. This will appear in the “Messages” window in SSMS.
  2. That will be followed by code that clears various caches to make all tests the same. Don’t run this code on a production box.
  3. The next section is the actual test code. It turns on STATISTICS TIME , which will appear also appear in the “Messages” tab. The first comment in this section will also appear in the “TextData” column if you’re running SQL Profiler on whatever SPID your testing from.
  4. Each test will be executed 3 times using a “GO 3” to test for an "Empty Cache" condition, 2nd run when the execution plan is actually stored (if you have Optimize for Ad Hoc queries enabled, and you usually should), and a fully cached 3rd run.
  5. We’ll run two tests, one after the other. The first test will use the FORMAT() function and the second test will use something other than FORMAT().

The run results of each pair of tests will be included in this article as they appear in SQL Profiler. You can see similar results for CPU and Duration in the messages window if you don’t want to fire up SQL Profiler when doing your own testing.

Also note… displaying a million rows takes a certain amount of time, no matter what. I call that the "Great Equalizer" when it comes to measuring performance of code. To overcome that problem, I’ve removed all display time from the equation by dumping the output to an “@BitBucket” variable so that we can see what the real computational differences are.

Test #1 – Convert to a “MM/DD/YYYY” format.

This uses a very straight forward FORMAT() and an equally straight forward CONVERT using the “101” style.

(Video) Formatting Code in ADS with SQL Prompt

Here’s the code…


… and here are the SQL Profiler results:

How to Format Dates in SQL Server (Hint: Don’t Use FORMAT!) – SQLServerCentral (1)

Taking the average CPU and Duration of each group of tests, it turns out that the CONVERT 101 method is a whopping 27.8 times faster for both CPU and Duration!

Test #2

Many folks will balk at the first test and say that it was too easy and the CONVERT was built to do that. What about a more complicated format where CONVERT doesn’t have an equivalent “style”?

--=====================================================================================PRINTREPLICATE('=',119);RAISERROR('==========FORMAT(D)==========',0,0)WITHNOWAIT;--ThisislikeFriday,February25,2028--=====================================================================================SETSTATISTICSTIME,IOOFF;CHECKPOINT;DBCCFREEPROCCACHEWITHNO_INFOMSGS;DBCCDROPCLEANBUFFERSWITHNO_INFOMSGS;GO--===== FORMAT(D) =====================================================================SETSTATISTICSTIMEON;DECLARE@BitBucketVARCHAR(50);SELECT@BitBucket=FORMAT(SomeDT,'D')--Adapts to language but is "unpredictable". FROM dbo.FormatTest--For example, removes commas in French.;SETSTATISTICSTIME,IOOFF;PRINTREPLICATE('-',119);GO3--=====================================================================================PRINTREPLICATE('=',119);RAISERROR('==========BRUTEFORCE==========',0,0)WITHNOWAIT;--=====================================================================================SETSTATISTICSTIME,IOOFF;CHECKPOINT;DBCCFREEPROCCACHEWITHNO_INFOMSGS;DBCCDROPCLEANBUFFERSWITHNO_INFOMSGS;GO--=====BRUTEFORCE===================================================================SETSTATISTICSTIMEON;DECLARE@BitBucketVARCHAR(50);SELECT@BitBucket=DATENAME(dw,SomeDT)+','+DATENAME(mm,SomeDT)+''+DATENAME(dd,SomeDT)+','+DATENAME(yy,SomeDT)FROMdbo.FormatTest;SETSTATISTICSTIME,IOOFF;PRINTREPLICATE('-',119);GO3

Here are the SQL Profiler results from that:

How to Format Dates in SQL Server (Hint: Don’t Use FORMAT!) – SQLServerCentral (2)

(Video) Delete Install & Configure SQL Server with PowerShell DSC | Jess Pomfret | WIT?

Again, doing the math of taking the averages of CPU and Duration for each set of runs, we find that the “Brute Force” method takes 17.3 times less CPU and runs 17.7 times faster than FORMAT() does.

SQL Death by a Thousand Cuts

I’ve heard it a thousand times… “But it doesn’t matter because I’m only doing formatting for 1 or a couple of rows at a time”.

Ladies and Gentlemen, performance ALWAYS matters and is second only to accuracy and it’s a very, very close second. Stop and think about it…

First, how many times per hour are such “low row count” queries going to run in an hour?

Second, what would it be like if everything on your server ran even just 2 to 3 times faster, never mind 17 to 27 times faster?

Third, now that you know the right way, why would you intentionally do it the wrong way?

Oh! I see… “Developers time is valuable”. If your Developers don't know how to do this type of thing and do it quickly, there's a simple solution... GET THEM SOME TRAINING!... especially in the areas of performance. You won't be sorry you did. If you're a Developer, spend some time at self-training. It's a great investment in your career.

(Video) Operational Validation of SQL Server at Scale with PowerShell and Jenkins with Cody Konior

Just stop using FORMAT()… server time is just as valuable Developer time and it’s valuable 24/7/365 even when the Developers are sleeping.

And the “Death by a Thousand Cuts” is, if you always take the attitude that you’re only working with a couple of rows and forget about performance in all those low-row applications, your server is going to be 17 to 27 times slower and, to fix it, you’re going to have to fix it in a thousand places.

Wrap Up

FORMAT() is seriously slow and CPU intensive compared to the more traditional methods of converting Dates and Times to strings in SQL Server/T-SQL. Use CONVERT or DATENAME with some concatenation instead.

Here are some links for CONVERT and DATENAME that can help you with this issue as well as many other areas of your code. And don’t stop at just date conversions for CONVERT… it’s a bit long but there’s a wealth of information in that article about many things even including thing like which conversions are NOT deterministic and much more!

And, there’s some good utility in DATEPART, as well…

And, as a bit of a sidebar, the STR() function is also slow (about 3 times as slow as other methods) and has other issues, as well. Please see the following article on that. It's old but still very relevant.


Thanks for listening, folks!

(Video) How does SQL Server store that data type? - Randolph West

-- Jeff Moden

1 "RBAR” is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"

© Copyright - Jeff Moden,03 Jul 2022, All Rights Reserved


How would you format SQL Server dates? ›

SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD.
SQL Date Data Types
  1. DATE - format YYYY-MM-DD.
  2. DATETIME - format: YYYY-MM-DD HH:MI:SS.
  4. YEAR - format YYYY or YY.

How do I format a date in mm/dd/yyyy in SQL? ›

SQL Date Format with the FORMAT function
  1. Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, etc. ...
  2. To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date.
Oct 13, 2021

How to change date format in SQL from MM DD YYYY to DD MM YYYY? ›

Approach 1: use the Format function to show date in the required format. FORMAT(Table[Date Column], "DD/MM/YYYY"). Approach 2: select your date field then go to modelling tab then change the data format option according to DD/MM/YYYY.

What is the default format for date data type in MySQL? ›

MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' .

How do I change the date format in a database? ›

We change the date format from one format to another. For example - we have stored date in MM-DD-YYYY format in a variable, and we want to change it to DD-MM-YYYY format. We can achieve this conversion by using strtotime() and date() function. These are the built-in functions of PHP.

How do you convert date format from YYYY MM DD to Yyyymmdd in SQL? ›

For the data load to convert the date to 'yyyymmdd' format, I will use CONVERT(CHAR(8), TheDate, 112). Format 112 is the ISO standard for yyyymmdd.

How do you change YYYY MM DD format? ›

Press CTRL+1. In the Format Cells box, click the Number tab. In the Category list, click Date, and then choose a date format you want in Type.

How do you change the input type date in dd MM yyyy format? ›

To set and get the input type date in dd-mm-yyyy format we will use <input> type attribute. The <input> type attribute is used to define a date picker or control field. In this attribute, you can set the range from which day-month-year to which day-month-year date can be selected from.

How do I format text to date in SQL? ›

Use the function TO_DATE() to convert a text value containing a date to the date data type. This function takes two arguments: A date value. This can be a string (a text value) or a text column containing date information.

What is the default formatting for dates? ›

Date format is MM/DD/CCYY where MM represents a two-digit month, DD represents a two-digit day of the month, CC represents a two-digit century, and YY represents a two-digit year. The date separator may be one of the following characters: '/', '-' or ','. This is the default date format.

What is the default format for date data type? ›

The default format for the DATE data type is YYYY-MM-DD. YYYY represents the year, MM represents the month, and DD represents the day. The range of the date value is between 0001-01-01 and 9999-12-31. The TIME data type consists of hour, minute, and second information to represent a time value.

What is the default format of date data type *? ›

Remarks. Use the Date data type to contain date values, time values, or date and time values. The default value of Date is 0:00:00 (midnight) on January 1, 0001.

What is difference between MM and MM in date format? ›

The values should be 'mm' for minute and 'MM' for month.

How to change date format from yyyy mm dd to dd mm yyyy in JavaScript? ›

Re: convert Date from YYYY-MM-DD to MM/DD/YYYY in jQuery/JavaScript. var tempDate = new Date("2021-09-21"); var formattedDate = [tempDate. getMonth() + 1, tempDate. getDate(), tempDate.

How do you change date format from mm/dd/yyyy to mmm yyyy? ›

First, pick the cells that contain dates, then right-click and select Format Cells. Select Custom in the Number Tab, then type 'dd-mmm-yyyy' in the Type text box, then click okay. It will format the dates you specify.

How do you format date in input type date? ›


How do I change the value of a input type date? ›

Input Date value Property
  1. Set a date for a date field: getElementById("myDate"). value = "2014-02-09";
  2. Get the date of a date field: var x = document. getElementById("myDate"). value;
  3. An example that shows the difference between the defaultValue and value property: getElementById("myDate"); var defaultVal = x.

How do I change date format in text? ›

Convert text dates by using the DATEVALUE function

Select a blank cell and verify that its number format is General. Click the cell that contains the text-formatted date that you want to convert. Press ENTER, and the DATEVALUE function returns the serial number of the date that is represented by the text date.

What is the standard date format in SQL? ›

The default string literal format, which is used for down-level clients, complies with the SQL standard form that is defined as YYYY-MM-DD. This format is the same as the ISO 8601 definition for DATE.

How do you format a date? ›

Many people get confused about how to write dates with commas, so here is a rule of thumb: in the month-day-year format (used in the United States), place commas after the day and year. In the day-month-year format (used in the UK and other countries), do not use commas at all. On May 13th, 2007 Daniel was born.

Is there a standard date format? ›

The ISO standard takes a general-to-specific approach for its date formats: The year comes first, followed by the month and then by the day of the month, with most elements represented as numerical values. For example, the ISO representation for July 15, 2022 is either 20220715 or 2022-07-15.

What is simple date format? ›

SimpleDateFormat is a concrete class for formatting and parsing dates in a locale-sensitive manner. It allows for formatting (date → text), parsing (text → date), and normalization. SimpleDateFormat allows you to start by choosing any user-defined patterns for date-time formatting.

How do I format a date in a string? ›

Let's see the simple code to convert String to Date in java.
  1. import java.text.SimpleDateFormat;
  2. import java.util.Date;
  3. public class StringToDateExample1 {
  4. public static void main(String[] args)throws Exception {
  5. String sDate1="31/12/1998";
  6. Date date1=new SimpleDateFormat("dd/MM/yyyy").parse(sDate1);

How do you change the date format in input type text? ›

To set and get the input type date in dd-mm-yyyy format we will use <input> type attribute. The <input> type attribute is used to define a date picker or control field. In this attribute, you can set the range from which day-month-year to which day-month-year date can be selected from.


1. SQL Server Central Webinar #26 - Best Practices in Database Deployment (Part 2)
(Redgate Videos)
2. What's new in SQL Server 2022
(Microsoft Mechanics)
3. SQL Server & Containers with Andrew Pruski
4. Top Tips for Writing Better T-SQL Stored Proc- GF
(Redgate Videos)
5. Common Problems in Backup & Recovery with Grant Fritchey - SQL in the City LDN 2013
(Redgate Videos)
6. SQL Prompt column picker
(Redgate Videos)


Top Articles
Latest Posts
Article information

Author: Catherine Tremblay

Last Updated: 19/06/2023

Views: 6609

Rating: 4.7 / 5 (67 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Catherine Tremblay

Birthday: 1999-09-23

Address: Suite 461 73643 Sherril Loaf, Dickinsonland, AZ 47941-2379

Phone: +2678139151039

Job: International Administration Supervisor

Hobby: Dowsing, Snowboarding, Rowing, Beekeeping, Calligraphy, Shooting, Air sports

Introduction: My name is Catherine Tremblay, I am a precious, perfect, tasty, enthusiastic, inexpensive, vast, kind person who loves writing and wants to share my knowledge and understanding with you.