Convert DATETIME, trim leading zeros

Question

Convert DATETIME, trim leading zeros

Viewing 3 posts - 16 through 17 (of 17 total)

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

drew.allen SSC Guru Points: 76988 More actions · Answer 1

thisisfutile - Wednesday, March 29, 2017 2:26 PM
I sure hope this isn't considered bad etiquette by reviving a 5 year old thread but the reply button was available so... 😉
I'm like Crissy, I'm stuck in Tsql so I'll need to formate a date using functions. Does anyone have an opinion concerning speed/performance on Paul White's excellent idea of 'FORMAT' (I'm on 2012 and wasn't aware of this function) versus Jeff's suggestions of STUFF or SUBSTRING?
Thank you!

It's usually better to create a new thread and reference the old thread.

To answer your question. FORMAT is horribly expensive. DO NOT use FORMAT.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

thisisfutile - Wednesday, March 29, 2017 2:26 PM
I sure hope this isn't considered bad etiquette by reviving a 5 year old thread but the reply button was available so... 😉
I'm like Crissy, I'm stuck in Tsql so I'll need to formate a date using functions. Does anyone have an opinion concerning speed/performance on Paul White's excellent idea of 'FORMAT' (I'm on 2012 and wasn't aware of this function) versus Jeff's suggestions of STUFF or SUBSTRING?
Thank you!

FORMAT is 44 times slower than CONVERT. I've not tested the alternate solution that I've posted on this thread but I do know that REPLACE and STUFF are nasty fast and probably won't add much time to it all. If I get some time, I'll test it on a million row table and see what happens.

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

thisisfutile Hall of Fame Points: 3668 More actions · Answer 3

Thanks, Drew and Jeff. I only did one test and it was by no means a Jeff-Moden-esque test. I had already written a query against a table with a date column and I had some functions in the Select line. I then read this thread, added FORMAT to my select line and it seemed to cause the query to run a little slower. Your reply confirms my suspicion so no need to do testing, at least not for my sake. Thanks again!