autoexcrement (12/9/2010)
Wayne, yes, thank you. That explains it completely. So, using your example of a time "80101", the code would (working from the inside out) transform it thusly:80101 (original)
10108 (reversed)
10:108 (stuff 3rd position with colon)
10:10:8 (stuff 6th position with colon)
8:01:01 (re-reverse)
So, all that remains to be explained is...are you guys some kind of freaking sql wizards or what? What hope is there for us normal people with you lot lurking around? 🙂
Thanks, I continue to be in awe at the creativity, intelligence, and amazing patience and tutelage available on this site.
EDIT: P.S. Your double-stuffed remix is tasty too!
One thing to note: The order that you do the STUFF calls can change what you're doing.
Above, you inserted the colons from left to right. What this means is that you have to account for that colon in the subsequent calls to STUFF.
The alternative is to insert them from right-to-left. Showing just them, it would be:
1010:8 (stuff 5th position with colon)
10:10:8 (stuff 3rd position with colon)
You'll find a lot of folks will do it this way just to take that variable out of the way.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes