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!)

August 10, 2022 at 1:43 pm

This was removed by the editor as SPAM

Viewing 9 posts - 16 through 23 (of 23 total)

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

frederico_fonseca SSCoach Points: 16154 More actions · Answer 1

see this https://stackoverflow.com/questions/761121/performance-issue-comparing-to-string-format for a small compare rewrite - very specific but it kind of addresses performance.

and then https://hashnode.robinrottier.com/net-6-string-format-performance and https://devblogs.microsoft.com/dotnet/string-interpolation-in-c-10-and-net-6/

main issue with "format" is that it has soo much power to it and some of the of the time is spent trying to figure out what it has to do before it hands over the work to the lower functions - and some of them aren't "friendly" to performance (mainly some do eat memory)

but yes in C# you don't see the same impact as we see in SQL due to volume.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Jeff Moden wrote:

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.

Hi Jeff, I enjoyed the article. Can't remember how many years ago it was that I stopped using FORMAT(), but no doubt it was because of something I read on this forum.

As I have nothing particular to add in terms of technical stuff, I decided to do some proofing for you.

---------------------------------------------------------------------------------

Test Code Structure

"will appear also appear" - remove first appear

"whatever SPID your testing from" - you're

SQL Death by a Thousand Cuts

"server time is just as valuable Developer time" - as valuable as

Wrap Up

"even including thing like" - things

---------------------------------------------------------------------------------

Also, while I know that 24/7/365 is a popular way of saying 'every day', I'd suggest that, as an analytical type person, you consider avoiding it in future – the numbers don't add up.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Also, I think that 'SQL Death by a Thousand Cuts' would be a great article in its own right.

Trying to convince application developers that it's worth spending the time to optimise their queries and having them respond with "it's only a few rows of data" is like flogging a dead horse. Probably worse – at least you get to de-stress while flogging a dead horse.

Being able to direct them to an article which demonstrates why it's worth doing would be most helpful. I'd probably add it to my signature section.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ryan C. Price Ten Centuries Points: 1184 More actions · Answer 4

Jeff Moden wrote:

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.

Perhaps a simple compromise would be for SQL Server to recognise the supported formats supplied as "format" strings. e.g. as well as CONVERT(VARCHAR(30),GETDATE(),101), allow CONVERT(VARCHAR(30),GETDATE(),'mm/dd/yyyy'). There would be a lookup as part of query parsing, and if an unknown format is supplied, the same error is returned as if you currently get when you supply an invalid number.

Or, alternatively, have some meaningful constants that could be used, e.g.:

DATEFORMAT_US_SHORT = 1
DATEFORMAT_GB_SHORT = 3
DATETIMEFORMAT_US = 22
DATEFORMAT_US_LONG = 101
DATEFORMAT_GB_LONG = 103
DATETIMEFORMAT_ISO8601 = 126

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

That's not a bad idea, Ryan. It would be better, I think to name things like DATEFORMAT_MM/DD/YYYY but, of course, the won't work in the name without brackets.

The real issue here is familiarity with the tools you're using. A Front-End Developer might know exactly what FORMAT('D') is where a DB Developer might have to look it up. Why is it so difficult for the reverse to be true?

What would be even better is if FORMAT was wasn't the performance problem that it is. I was looking at the first link that Frederico posted above and it seems like it's also an issue in .Net code, as well, but most people in the Front End world don't seem to notice because many don't work with the volume of data that database people do. They usually only see on row at a time and have nothing to compare it to and they haven't done a "Tolerance Buildup" study of what happens when it's used a million times per hour, perhaps more than once for each time. That's not a fault on their part. It's just not the nature of their job and I can't blame them one bit for that.

As a bit of a sidebar, if you actually understand all the languages and dialects that FORMAT easily handles, it's quite the generic tool. That's the real issue, though... generic tools will never be faster than a specific purpose tool such as CONVERT(101).

Good feedback on this subject, Ryan. Thanks for sharing your thoughts.

--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: 1004340 More actions · Answer 6

@ Frederico - Thank you for the links. I've not yet read through them all but that first one is sure interesting.

@ Phil - Thanks for the proof reading. I made a bit of a train wreck in the first recent article. Would you be interested in being my proof-reader for such articles? If so, PM me with an email address because I sure could use the help there. And, I know we're all busy so it's ok with me (I DO understand!) if the answer is "No".

--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)

Theo Ekelmans SSCarpal Tunnel Points: 4593 More actions · Answer 7

Hmmm.....

Interesting article Jeff!

For the life of me, i would never have considered the impact of Format on out larger OLTP loads.

Knowing you being rather thorough, one question question was left unanswered: how about using a .net assembly for high volume data conversion? Usually you have only your local date time to convert to, so brute forcing that in .net wouldn't that be even faster?

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

Theo Ekelmans wrote:

Hmmm.....
Interesting article Jeff!
For the life of me, i would never have considered the impact of Format on out larger OLTP loads.
Knowing you being rather thorough, one question question was left unanswered: how about using a .net assembly for high volume data conversion? Usually you have only your local date time to convert to, so brute forcing that in .net wouldn't that be even faster?

Hi Theo. Thanks for the read and the reply.

My answer to your question is that doing it in .Net probably won't be any faster especially if you use the FORMAT function in .net. It's the same"dll" that SQL Server uses and, from the first article that I'm seeing from the link collection that Frederico was kind enough to supply, the FORMAT function appears to have similar performance outside of SQL Server.

I think that a well written and non-generic bit of code to do specific checks would be a whole lot faster. People tend to forget about the number of times something happens in OLTP and you have to consider even microseconds in "high volume high hit" environments. There's been a lot of heavy hitters that have "When milliseconds matter" presentations on that very subject.

And, good "seeing" you again... haven't seen you around since the end of last year.

And, if you read the previous version of this particular post, I've edited it to address only OLTP, which is what your question centered on.

This reply was modified 2 years, 3 months ago by Jeff Moden.

--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)