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

Question

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

Jeff Moden

SSC Guru

Points: 1003863
More actions
August 5, 2022 at 12:00 am

#4066629

Comments posted to this topic are about the item How to Format Dates in SQL Server (Hint: Don’t Use FORMAT!)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
August 5, 2022 at 1:13 pm

This was removed by the editor as SPAM
August 6, 2022 at 9:09 am

This was removed by the editor as SPAM
August 7, 2022 at 11:55 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

My apologies... there's a missing word as follows....

"Each test will have the following nearly sections:" should have been "Each test will have the following nearly identical sections:"

And I left an "s" off of "thinks" in one spot, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

henn SSC Veteran Points: 218 More actions · Answer 2

FORMAT versus CONVERT

There is no need (or rare need) to use formating date2string in high volume/performance cases on database layer. Real word conversions will be done on business (DW loads?) or user interface layer (apps)
On cases when really need to convert - performance isn't the most critical point
Only FORMAT have language (local) specific option (some about PARSE) - CONVERT support only Colonialization (possible localization limited mainly Colonial countries)

tboos Newbie Points: 3 More actions · Answer 3

MS needs to improve the performance of the Format command - a Convert(101) makes NO sense when reviewing code for date formatting problems - unlike the format which is highly understandable (and maintainable). We all agree performance is important, but not at the cost of mysterious functions. Thanks for the article.

ratbak Ten Centuries Points: 1333 More actions · Answer 4

Convert(101) makes NO sense when reviewing code for date formatting problems - unlike the format which is highly understandable (and maintainable). We all agree performance is important, but not at the cost of mysterious functions.

Please explain what makes no sense about convert, and what is mysterious?

CONVERT has existed for decades and is thoroughly documented. While I'd agree the style codes are not intuitive, I'd hardly call them mysterious. And though it may not be true for larger, international organizations, I find that I need only a few styles 90% of the time, and it's easy to look up the others when I need a different one.

And despite having used format occasionally for years in SSRS & C#, I still often had to refer back to the documentation.

SQLOzzie Default port Points: 1441 More actions · Answer 5

Along the same lines, what techniques do people use to inject commas into, say, @@ROWCOUNT vs the FORMAT statement - FORMAT( @@ROWCOUNT ), '###,###,###,##0' ) AS RowCount

I still find it hard to believe the output 'naturally' comes out unformatted - maybe my eyes are getting older ☺

frederico_fonseca SSCoach Points: 16017 More actions · Answer 6

for majority of uses within SQL Server itself you don't need the numerics formatted that way so as mentioned before the formatting should be done on the receiving UI - this assuming it is to be displayed to a user.

those that wish to use the output of SSMS to be user friendly can in most cases use Excel to do it - where it will be all pretty and wonderful for the users - otherwise there's no need to have it formatted.

for the very weird cases where you need to product a formatted txt file directly from SQL Server then using a function to format the numbers and/or dates is easy enough to build.

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 7

SQLOzzie wrote:

Along the same lines, what techniques do people use to inject commas into, say, @@ROWCOUNT vs the FORMAT statement - FORMAT( @@ROWCOUNT ), '###,###,###,##0' ) AS RowCount
I still find it hard to believe the output 'naturally' comes out unformatted - maybe my eyes are getting older ☺

This would be a similar case as using a scalar function - if you really do need a formatted row count at the end of a query then FORMAT or a scalar function would be reasonable and useful. It would not necessarily have a negative impact on the overall query (unless used in a loop/cursor where it could have a cumulative effect).

With that said - using FORMAT in a query is going to have a negative impact on the performance of that query. And in general - the built-in functions are going to provide the same output with much less of an impact. And - if there is a specific localization requirement then an inline-table valued function using the built-in functions to return that data would still be much more performant than using FORMAT.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

henn wrote:

FORMAT versus CONVERT
There is no need (or rare need) to use formating date2string in high volume/performance cases on database layer. Real word conversions will be done on business (DW loads?) or user interface layer (apps)

Correct. I stated that in the article in different words.

henn wrote:

On cases when really need to convert - performance isn't the most critical point

As I stated in the article, performance is never the most critical point. Accuracy is. But... performance is a very close second and I've found that when you really do need to format anything, it's usually either for large batches or something that gets hit 10's of thousands of times per hour and so, while performance is second, it's still and always will be critical.

The trouble is that people justify using FORMAT because they probably are working with one row at a time and can't see the big picture and don't know what "Death by a Thousand Cuts" actually means in the world of SQL.

I'll also state that if you use CONVERT instead of FORMAT all the time, you'll get really, really good at it and the side benefit is that your code will always run faster than the crowd that's using FORMAT.

Or not... if you want to continue using FORMAT, go ahead. You know the ramifications. It's your choice. But it's an "easy kill" to be 17.7 times faster per column that you format

henn wrote:

Only FORMAT have language (local) specific option (some about PARSE) - CONVERT support only Colonialization (possible localization limited mainly Colonial countries)

Again as as you and I have both stated, those times when such a thing is needed should generally be reserved for the presentation level so that the local dialect of date formats can take place almost automatically. I'll also state that if you have targeted deliveries that require localization of date formats, it's easy enough to do with CONVERT. You just need to know where you're sending stuff and make it so. It's these places where you can look the part of a hero by using better techniques and not just including how you format dates and numeric values.

Thanks for the read and thank you for the feedback and I mean that seriously and nicely. You're one of the very few out of more than 11,000 in the last 36 hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

tboos wrote:

MS needs to improve the performance of the Format command - a Convert(101) makes NO sense when reviewing code for date formatting problems - unlike the format which is highly understandable (and maintainable). We all agree performance is important, but not at the cost of mysterious functions. Thanks for the article.

I 100% agree. It's a wonderful function for ease of use. If it worked much faster, I wouldn't have written the article. As for reviewing code, most people never have an issue with it because it's usually not the final format that matters in what they're looking for. If it actually is what they're looking for, then it only takes seconds to either look it up or write SELECT CONVERT(VARCHAR(30),GETDATE(),101).

Usually, folks never have to do that with my code because I put in a comment like -- mm/dd/yyyy in the code next to the convert.

If I know I'm going to be reviewing a lot of code where the format of the dates is important, then I'll copy and paste the convert chart into a document and then I can lookup "101" or whatever almost instantaneously. I think I'd only have to do that once or twice to remember it long enough to make it through the code.

And I disagree on FORMAT being so very readable because of the format shortcuts smarties use like "D". Figure that one out if you aren't familiar with it.

And, to be sure, if FORMAT wasn't at least 17.1 times slower, I'd be using it, for sure. It's like the STR() function... it's usually only about 3 times slower than CONVERT but I still won't use it because it's just another slice in the "Death by a thousand cuts". In my mind, I ask, why do it wrong when you know how to easily do it right?

And I have to say the same to you as I did to the person in my last post... Thanks for the read and thank you for the feedback and I mean that seriously and nicely. You're one of the very few out of more than 11,000 in the last 36 hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 10

SQLOzzie wrote:

Along the same lines, what techniques do people use to inject commas into, say, @@ROWCOUNT vs the FORMAT statement - FORMAT( @@ROWCOUNT ), '###,###,###,##0' ) AS RowCount
I still find it hard to believe the output 'naturally' comes out unformatted - maybe my eyes are getting older ☺

I'm kinda glad they don't waste the clock-cycles on "naturally" formatting anything. Like I and many others have said, I let the presentation layer do that and any formatting they do would turn it into a string, which I frequently don't want. I will admit that it would be nice if they formatted the NNNNNNN Row(s) Returned thing but then they would have had to make that language sensitive, etc, etc.

My question is, how did you do such formatting before FORMAT came out? For the few things that I need such formatting for, I created an iTVF along time ago and still use that today.

And, like the others, I have to thank you for both the read and the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 11

ratbak, Frederico, and Jeffrey... the same goes for you guys. You always get involved and you always try to make a difference. Thank you for your valuable/though provoking observations, discussion, and the awesome answers you come up with on posts. I'm glad to "know" you all.

Shifting gears a bit, does anyone know if FORMAT has similar performance issues when it's used in its native .NET environment? My guess would be "Yes" but not many people work with as many rows in .Net as we do in SQL Server and so they never notice and never have reason to do such a test..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)