RE: Convert DATETIME, trim leading zeros

SSC Guru

Points: 1003867

March 29, 2017 at 5:00 pm

#1935623

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)