Using FORMAT() for Dates

Question

Using FORMAT() for Dates

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
May 24, 2018 at 12:03 am

#347896

Comments posted to this topic are about the item Using FORMAT() for Dates

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

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

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 2

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.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 3

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)

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 4

Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.

Sixshooter seems like an overkill unless the user is very "thick"😀
😎

Thom A SSC Guru Points: 99126 More actions · Answer 5

Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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

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

Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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)

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 7

Thom A - Thursday, May 24, 2018 6:03 AM
Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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😉
😎

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 8

Jeff Moden - Thursday, May 24, 2018 6:47 AM
Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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)
😎

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

Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.

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.

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

Eirikur Eiriksson - Thursday, May 24, 2018 7:30 AM
Thom A - Thursday, May 24, 2018 6:03 AM
Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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)

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 11

Jeff Moden - Thursday, May 24, 2018 8:02 AM
Eirikur Eiriksson - Thursday, May 24, 2018 7:30 AM
Thom A - Thursday, May 24, 2018 6:03 AM
Eirikur Eiriksson - Thursday, May 24, 2018 5:43 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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?
Image result for self destruction mechanism

Luis Cazares SSC Guru Points: 183698 More actions · Answer 12

patrickmcginnis59 10839 - Thursday, May 24, 2018 7:59 AM
Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.
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.

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 13

Eirikur Eiriksson - Thursday, May 24, 2018 5:03 AM
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.

I'm not sure it's a permanent no-no, but certainly want to be careful here.

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

Jeff Moden - Thursday, May 24, 2018 5:21 AM
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.

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.