Using FORMAT() for Dates

Question

Post reply

Using FORMAT() for Dates

Viewing 15 posts - 31 through 45 (of 45 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 1

Jeff Moden - Thursday, May 24, 2018 3:07 PM

Heh.... so if you only lived 4 miles from work, you wouldn't mind paying $20 a gallon for gas? 😉

I guess I'll never understand why a lot of people excuse poor code or poor tools based on low usage or low row counts. These and other forums are littered with posts where such things happened and now are in serious trouble with performance. It takes no extra time to just do it right from the beginning.

You misunderstand. You're saying that it's easy, so I'm excusing poor code or tools. There's more to it than this, including the fact that FORMAT is easier to read and use than a mix of other string handling. There's also a knowledge and staff skill issue, which come into play, and mean that there are places where FORMAT() has essentially no impact on the system because there isn't enough data.

I can come up with analogies where over engineering is bad. This is overengineering when I have developers or DBAs without skills. Certainly I can teach them, but that takes time, and I can't teach them everything overnight.

It takes tons of extra time to do things right from the beginning when you lack knowledge.

I didn't write the "don't use this, do this instead" because there will always be people that want to use this. They won't listen to anything else, and will move on to some other place to figure out how to build the tank that shoots into the driver's seat. At least here I've given them a warning, so when things do go pear shaped, they may remember that and look for an alternative.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 2

patrickmcginnis59 10839 - Thursday, May 24, 2018 9:47 AM

Yeah if you are spending several hours a week because of format then that's what Steve cautioned against by warning about format's performance cost. I can also understand that there are folks who do not like trading programmer time for computer time, but often they change their tune when programmer time gets unaffordable. Format could be useful when the cost of using it has little impact, but this would probably NOT be the case if you are processing enough records to where the impact becomes a cost worth considering.

I know folks don't understand programmer time / computer time tradeoffs, but that's up to you. If programmer time is free then have at it!

If you're going to reference xkcd on this, then you could do worse than go to this one.

Thomas Rushton
blog: https://thelonedba.wordpress.com

x SSC-Insane Points: 23660 More actions · Answer 3

Jeff Moden - Thursday, May 24, 2018 3:07 PM

Steve Jones - SSC Editor - Thursday, May 24, 2018 9:58 AM

I'll also add that I've worked on lots of systems that trundled along at 8% CPU and few IOPS all day long. Those systems could be written with cursor processing of child/parent relationships and FORMAT() everywhere and do fine. Some of them likely do with EF code in the front end.

FORMAT() has a place. A limited place, but still, it could be good

Heh.... so if you only lived 4 miles from work, you wouldn't mind paying $20 a gallon for gas? 😉

I guess I'll never understand why a lot of people excuse poor code or poor tools based on low usage or low row counts. These and other forums are littered with posts where such things happened and now are in serious trouble with performance. It takes no extra time to just do it right from the beginning.

I think it takes a certain mindset to work in IT optimally, to understand tradeoffs, programmer time, machine time, priorities, etc, but I'm the first to admit the perfect mix of talent, wisdom, pragmatism, and ability to go from the big picture down to the nitpicking details is a hard mix to attain. In this certain case, I think folks are saying that we would use "format" in places that the costs of using it will not be a material concern. Obviously like I said, you'd have to be of that certain mindset to understand what "material concern" even means.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

ThomasRushton - Friday, May 25, 2018 5:20 AM

patrickmcginnis59 10839 - Thursday, May 24, 2018 9:47 AM

Yeah if you are spending several hours a week because of format then that's what Steve cautioned against by warning about format's performance cost. I can also understand that there are folks who do not like trading programmer time for computer time, but often they change their tune when programmer time gets unaffordable. Format could be useful when the cost of using it has little impact, but this would probably NOT be the case if you are processing enough records to where the impact becomes a cost worth considering.

I know folks don't understand programmer time / computer time tradeoffs, but that's up to you. If programmer time is free then have at it!

If you're going to reference xkcd on this, then you could do worse than go to this one.

With regards to the title of the toon at the link you provided, the answer is "You won't have to spend any extra time if you know the right way to do it to begin with". 😀

--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)
Intro to Tally Tables and Functions

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 5

Thomas Rushton

SSC-Insane

Points: 22648

May 25, 2018 at 8:45 am

#1991989

Jeff Moden - Thursday, May 24, 2018 3:33 PM

No problem...

Wait. I know this one. It's about multiple tempdb data files, isn't it...

Thomas Rushton
blog: https://thelonedba.wordpress.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

ThomasRushton - Friday, May 25, 2018 8:45 AM

Jeff Moden - Thursday, May 24, 2018 3:33 PM

No problem...

Wait. I know this one. It's about multiple tempdb data files, isn't it...

That or "crap code" that needed to scale out on extra servers so that it would run fast enough. A little poop here and a little poop there and suddenly you need to scale out. 😀

--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)
Intro to Tally Tables and Functions

x SSC-Insane Points: 23660 More actions · Answer 7

Jeff Moden - Friday, May 25, 2018 8:35 AM

ThomasRushton - Friday, May 25, 2018 5:20 AM

patrickmcginnis59 10839 - Thursday, May 24, 2018 9:47 AM

Yeah if you are spending several hours a week because of format then that's what Steve cautioned against by warning about format's performance cost. I can also understand that there are folks who do not like trading programmer time for computer time, but often they change their tune when programmer time gets unaffordable. Format could be useful when the cost of using it has little impact, but this would probably NOT be the case if you are processing enough records to where the impact becomes a cost worth considering.

I know folks don't understand programmer time / computer time tradeoffs, but that's up to you. If programmer time is free then have at it!

If you're going to reference xkcd on this, then you could do worse than go to this one.

With regards to the title of the toon at the link you provided, the answer is "You won't have to spend any extra time if you know the right way to do it to begin with". 😀

I think some of the posters don't think that format is entirely useless and I can agree with them. Its sort of funny, SQL actually does spend computer time to save programmer time BY DESIGN so I would think that SQL coders would know exactly whats trying to be said here but oh well 🙂

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

Steve Jones - SSC Editor - Thursday, May 24, 2018 4:31 PM

Jeff Moden - Thursday, May 24, 2018 3:07 PM

Heh.... so if you only lived 4 miles from work, you wouldn't mind paying $20 a gallon for gas? 😉

I guess I'll never understand why a lot of people excuse poor code or poor tools based on low usage or low row counts. These and other forums are littered with posts where such things happened and now are in serious trouble with performance. It takes no extra time to just do it right from the beginning.

You misunderstand. You're saying that it's easy, so I'm excusing poor code or tools. There's more to it than this, including the fact that FORMAT is easier to read and use than a mix of other string handling. There's also a knowledge and staff skill issue, which come into play, and mean that there are places where FORMAT() has essentially no impact on the system because there isn't enough data.

I can come up with analogies where over engineering is bad. This is overengineering when I have developers or DBAs without skills. Certainly I can teach them, but that takes time, and I can't teach them everything overnight.

It takes tons of extra time to do things right from the beginning when you lack knowledge.

I didn't write the "don't use this, do this instead" because there will always be people that want to use this. They won't listen to anything else, and will move on to some other place to figure out how to build the tank that shoots into the driver's seat. At least here I've given them a warning, so when things do go pear shaped, they may remember that and look for an alternative.

I DO appreciate the warning about the performance problem that you've given and the link to an article that proves it. Thanks for that.

I don't believe that demonstrating alternatives to using the more-than-an-order-of-magnitude slower performance issues with FORMAT is "over engineering". I believe and will stand by the thought that anyone that uses it, especially in light of the warning you've provided, is seriously "under engineering" and doesn't understand the concept of "tolerance buildups in code" leading to performance problems that can only be explained as a "death by a thousand cuts".

Looking at your examples and your comments on people not having skills or not knowing something, do you suppose that someone new to the task of developing in T-SQL (or C#) would immediately know that SELECT FORMAT(@dt, 'd') means to display the date with a short time (provided that it's not a part of a custom date format) or what they mean by a "short time"? They also wouldn't know that "D" means to display the date with a "long time" or that "f" means "Full Date/Short Time" or that "F" means "Full Date/Long Time" (provided that the operand is indeed a date/time datatype and not a decimal datatype) nor even what is meant by "short date" or "long date" without first having to learn it... just like they'd have to learn the much faster method of using CONVERT. That's no more readable than using a numeric format code if you don't actually know what those things mean. Heh... at least a 3 digit format code gives you a clue as to the datatype of the operand without having to go back to the top of the code to find out. If you're going to memorize something, memorize the faster method. 😉 I also find it annoying as to how many times you have to drill down into the documentation on FORMAT to find such things than you have to for CONVERT.

And, no, it doesn't take "tons of extra time to do things right from the beginning when you lack knowledge". It takes the same amount of time to learn both methods (Actually, it takes longer to learn FORMAT because of all the bloody drilldowns that I previously mentioned). It certainly doesn't take any extra time to write the code correctly and avoid the "tradeoff" because a tradeoff is simply not required to do it right the first time. Aren't you the one that has written so very many articles about the basis of learning something new? :blink: Let's learn how to do it with performance in mind, which is one of the most requested topics on these very forums.

As someone else on this thread said, finding someone with all the right stuff is a "hard mix" to find and there are only two ways that people can become a member of that "hard mix". Teach yourself what's right through experimentation or be shown how to do it right. When we're teaching something, I'm thinking that "right" should include "performance" and that a demonstration of how to easily attain it rather than justifying not achieving it would be the right thing to do.

Because of my experiences at work, I also greatly concern myself with the terribly misplaced justification of using something bad just because it's easy and the row count it's being used with is small. Let's say, as small as 1 for a single jab by a GUI. Very few people actually think of what happens when that jab is multiplied to 100,000 times an hour. In the long gone past, I was also one of those that never consider it. After what I've seen on the job over the last decade, I've become keenly aware of it, its affects on overall performance of the application and the server, and the huge pain and huge amount of time it takes to fix the "death by a thousand cuts". It takes almost no extra time (and, frequently, it actually takes less time) to do it right but can take months or even years to fix the hundreds or even thousands of places where people have taken a shortcut whether is was to protect the time of developers or what have you.

All that being said, I don't actually expect that you'll change your mind about any of this. I just wanted to offer a different take on the subject in case someone actually does take the time to read this discussion.

--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)
Intro to Tally Tables and Functions

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

patrickmcginnis59 10839 - Friday, May 25, 2018 9:59 AM

Jeff Moden - Friday, May 25, 2018 8:35 AM

ThomasRushton - Friday, May 25, 2018 5:20 AM

patrickmcginnis59 10839 - Thursday, May 24, 2018 9:47 AM

Yeah if you are spending several hours a week because of format then that's what Steve cautioned against by warning about format's performance cost. I can also understand that there are folks who do not like trading programmer time for computer time, but often they change their tune when programmer time gets unaffordable. Format could be useful when the cost of using it has little impact, but this would probably NOT be the case if you are processing enough records to where the impact becomes a cost worth considering.

I know folks don't understand programmer time / computer time tradeoffs, but that's up to you. If programmer time is free then have at it!

If you're going to reference xkcd on this, then you could do worse than go to this one.

With regards to the title of the toon at the link you provided, the answer is "You won't have to spend any extra time if you know the right way to do it to begin with". 😀

I think some of the posters don't think that format is entirely useless and I can agree with them. Its sort of funny, SQL actually does spend computer time to save programmer time BY DESIGN so I would think that SQL coders would know exactly whats trying to be said here but oh well 🙂

I guess that would be the difference between an SQL Coder and an SQL Developer. The Coder would say "Good enough for this use". The Developer would say "Let me spend the extra 60 seconds or less that it takes to do it right so that I don't unnecessarily burn 44 times more clock cycles and to set an example for others that might have to modify the code in the future".

--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)
Intro to Tally Tables and Functions

Eirikur Eiriksson SSC Guru Points: 182631 More actions · Answer 10

Piling on, there are at least 15 alternatives to the format function, all of which are at least 10 times faster, some are closer to 1000 times faster
😎

Quick summary




USE TEEST;

GO

SET NOCOUNT ON;

--https://qa.sqlservercentral.com/Forums/FindPost1955185.aspx

DECLARE @SAMPLE_SIZE BIGINT = 100000;

DECLARE @FIRSTDATE DATETIME = '17991231';


SELECT 'REPLICATE' AS FNAME, REPLICATE(@FIRSTDATE,1) AS DTSTR   UNION ALL 

SELECT 'UPPER'   AS FNAME, UPPER(@FIRSTDATE)        UNION ALL

SELECT 'LOWER'   AS FNAME, LOWER(@FIRSTDATE)        UNION ALL

SELECT 'RTRIM'   AS FNAME, RTRIM(@FIRSTDATE)        UNION ALL

SELECT 'TRANSLATE' AS FNAME, TRANSLATE(@FIRSTDATE,'','')    UNION ALL

SELECT 'REPLACE'   AS FNAME, REPLACE(@FIRSTDATE,'','')    UNION ALL

SELECT 'REVERSE'   AS FNAME, REVERSE(REVERSE(@FIRSTDATE))   UNION ALL

SELECT 'CONCAT'  AS FNAME, CONCAT('',@FIRSTDATE)      UNION ALL

SELECT 'CONCAT_WS' AS FNAME, CONCAT_WS('',@FIRSTDATE,'')    UNION ALL 

SELECT 'STUFF'   AS FNAME, STUFF(@FIRSTDATE,1,0,'')     UNION ALL 

SELECT 'LTRIM'   AS FNAME, LTRIM(@FIRSTDATE)        UNION ALL 

SELECT 'LEFT'    AS FNAME, LEFT(@FIRSTDATE,20)      UNION ALL 

SELECT 'RIGHT'   AS FNAME, RIGHT(@FIRSTDATE,20)       UNION ALL 

SELECT 'CONVERT'   AS FNAME, CONVERT(VARCHAR(20),@FIRSTDATE,0)  UNION ALL 

SELECT 'CAST'    AS FNAME, CAST(@FIRSTDATE AS VARCHAR(20))

x SSC-Insane Points: 23660 More actions · Answer 11

Jeff Moden - Saturday, May 26, 2018 12:25 AM

patrickmcginnis59 10839 - Friday, May 25, 2018 9:59 AM

Jeff Moden - Friday, May 25, 2018 8:35 AM

ThomasRushton - Friday, May 25, 2018 5:20 AM

patrickmcginnis59 10839 - Thursday, May 24, 2018 9:47 AM

Yeah if you are spending several hours a week because of format then that's what Steve cautioned against by warning about format's performance cost. I can also understand that there are folks who do not like trading programmer time for computer time, but often they change their tune when programmer time gets unaffordable. Format could be useful when the cost of using it has little impact, but this would probably NOT be the case if you are processing enough records to where the impact becomes a cost worth considering.

I know folks don't understand programmer time / computer time tradeoffs, but that's up to you. If programmer time is free then have at it!

If you're going to reference xkcd on this, then you could do worse than go to this one.

With regards to the title of the toon at the link you provided, the answer is "You won't have to spend any extra time if you know the right way to do it to begin with". 😀

I think some of the posters don't think that format is entirely useless and I can agree with them. Its sort of funny, SQL actually does spend computer time to save programmer time BY DESIGN so I would think that SQL coders would know exactly whats trying to be said here but oh well 🙂

I guess that would be the difference between an SQL Coder and an SQL Developer. The Coder would say "Good enough for this use". The Developer would say "Let me spend the extra 60 seconds or less that it takes to do it right so that I don't unnecessarily burn 44 times more clock cycles and to set an example for others that might have to modify the code in the future".

I think Steve mentioned the performance caution in his original article.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

Yes he did. I've acknowledged that in my posts a couple of times now and is a part of what we're discussing.

--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)
Intro to Tally Tables and Functions

tom.w.brannon Hall of Fame Points: 3956 More actions · Answer 13

tom.w.brannon

Hall of Fame

Points: 3956

May 26, 2018 at 4:17 pm

#1992063

Interesting examples, even if function to be avoided. But I am puzzled as to why the time on the last example (Universal Date Long Time) 6 hours different from the others?

Steve Jones - SSC Editor SSC Guru Points: 728159 More actions · Answer 14

tom.w.brannon - Saturday, May 26, 2018 4:17 PM

Interesting examples, even if function to be avoided. But I am puzzled as to why the time on the last example (Universal Date Long Time) 6 hours different from the others?

UTC is my time +6 hours.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

tom.w.brannon Hall of Fame Points: 3956 More actions · Answer 15

Steve Jones - SSC Editor - Tuesday, May 29, 2018 1:31 PM

tom.w.brannon - Saturday, May 26, 2018 4:17 PM

Interesting examples, even if function to be avoided. But I am puzzled as to why the time on the last example (Universal Date Long Time) 6 hours different from the others?

UTC is my time +6 hours.

Then why doesn't time match those for Universal Date Short Time and RFC 1123 r Date Time which also appear to be GMT times. And how would it know the base time zone of the datetime variable passed to it to know how to convert to GMT?