Using FORMAT() for Dates
-
Comments posted to this topic are about the item Using FORMAT() for Dates
-
Got to admit, I was really excited when FORMAT was introduced into SQL Server. I can never remember all the different style codes for dates when using CONVERT, so Date and Time Styles is probably one of my most visited pages in the documentation. It's much easier on the eyes as well. If you saw the two examples below, it's quite obvious what the first one is doing, but not the second:
SELECT FORMAT(GETDATE(),'dd/MM/yyyy');
SELECT CONVERT(varchar(10),GETDATE(),103);
They, effectively, return the same result but without referencing the page I've linked above, or you're very familiar with CONVERT's style codes you wouldn't know that.Your warning though, Steve, is spot on; the performance is terrible. It's another reason why I tend to do everything in the presentation. It's often much quicker, plus, changing the data type before getting to the presentation layer can create oddities. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Thanks for this Steve, nice write-up.
😎As it stands, format is on my permanent no-no list because of the terrible performance, although I haven't tested it on SQL Server 2017.
-
Nice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
--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 - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎 - Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AMJeff Moden - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎You've clearly never dealt with a user before Eirikur... :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AMJeff Moden - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎Heh... ah... but it does have a low row count! 😀
--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) - Thom A - Thursday, May 24, 2018 6:03 AMEirikur Eiriksson - Thursday, May 24, 2018 5:43 AMJeff Moden - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎You've clearly never dealt with a user before Eirikur... :hehe:
I've dealt with many many thousands of users but then again, I don't design systems in this way😉
😎 - Jeff Moden - Thursday, May 24, 2018 6:47 AMEirikur Eiriksson - Thursday, May 24, 2018 5:43 AMJeff Moden - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎Heh... ah... but it does have a low row count! 😀
Still it is a kind of a RBAR solution, one can do this in a single set (read shot)
😎 - Jeff Moden - Thursday, May 24, 2018 5:21 AM
Steve gave the performance warning. Sometimes programmer time is traded for computer time, and often workloads are low and predictably so. I think there's a school of thought that every bit of code needs to be as optimized as possible and that's fine for where it fits, and given T-SQL's bad interpretive performance its a good idea to be extra cautious with SQL Server.
Sometimes your code simply cannot avoid the iterative penalty of T-SQL and in those cases format wouldn't be disqualified out of hand because its performance problems are dwarfed by the so called "RBAR" hit.
- Eirikur Eiriksson - Thursday, May 24, 2018 7:30 AMThom A - Thursday, May 24, 2018 6:03 AMEirikur Eiriksson - Thursday, May 24, 2018 5:43 AMJeff Moden - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎You've clearly never dealt with a user before Eirikur... :hehe:
I've dealt with many many thousands of users but then again, I don't design systems in this way😉
😎C'mon... such a thing is easy to scale up. 😀
--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 - Thursday, May 24, 2018 8:02 AMEirikur Eiriksson - Thursday, May 24, 2018 7:30 AMThom A - Thursday, May 24, 2018 6:03 AMEirikur Eiriksson - Thursday, May 24, 2018 5:43 AMJeff Moden - Thursday, May 24, 2018 5:21 AMNice write-up but, even with the warnings, I'm reminded of people that justify poor programming techniques or functionality because of low row counts. It's a bit like teaching people how to use a gun that looks like this.
Sixshooter seems like an overkill unless the user is very "thick"😀
😎You've clearly never dealt with a user before Eirikur... :hehe:
I've dealt with many many thousands of users but then again, I don't design systems in this way😉
😎C'mon... such a thing is easy to scale up. 😀
You owe me yet another keyboard Jeff (not charging for the pint I spilled over it)
😎Where do you find such "clever" images? Could be very useful for some presentations!
BTW, I like more to design to a scale out, any suggestion? - patrickmcginnis59 10839 - Thursday, May 24, 2018 7:59 AM
I've never understood the option to save programmer time by not caring about computing time. An extra hour spent by a programmer on writing good code could save several hours a week when the process is released to production. I've seen it and I'm being very conservative as improvements can be millions to one when comparing hours spent in processing vs programming.
- Eirikur Eiriksson - Thursday, May 24, 2018 5:03 AM
I'm not sure it's a permanent no-no, but certainly want to be careful here.
- Jeff Moden - Thursday, May 24, 2018 5:21 AM
I guess, but what's the alternative? Don't talk about it? Let people stumble on it? I'd like to think they stumble on my article and read the warning. At least then it might stick in their mind when they do something and it doesn't perform.
You're welcome to write a , don't use FORMAT(), Do This article, but complaints don't get read or spread as widely as information.
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply