Agreed. The CONVERT syntax is rubbish, but the output is so much more powerful. I'd rather have an equivalent of Date.ToString("YYYY-mmm-DD hh:mi:ss.ssss") in SQL but, sadly.... It could be written as a UDF but I'm not sure performance would be up to production use.
I had a wonderful stack of little helper functions and views I'd cobbled together at that job and had had free rein to stick into the real databases; they made development so much easier. The downside of employment though, that sort of work gets stuck with each employer you do it for and can't so easily be carried around like a toolkit.
I experimented a bit with some of the code from http://www.simple-talk.com/community/blogs/philfactor/archiv... a while back on some toy projects; it makes some interesting reading and had me building a few similar functions around my own conventions and rules. On a larger scale, I would be sorely tempted to build something like that into the Model database and insist on a clean output as part of the approval criteria.
It could be written as a UDF but I'm not sure performance would be up to production use.
If you ever find yourself in that situation again, write the UDF as a in-line table-valued function. You get multiple return values and avoid the scalar UDF hit, since the engine will execute it in-line (appropriately enough).
eg:
select t.date, d.date_as_string
from table1 t
cross apply dbo.format_date(t.date,'yyyy-mm-dd') d
I had a wonderful stack of little helper functions and views I'd cobbled together at that job and had had free rein to stick into the real databases; they made development so much easier. The downside of employment though, that sort of work gets stuck with each employer you do it for and can't so easily be carried around like a toolkit.
I experimented a bit with some of the code from http://www.simple-talk.com/community/blogs/philfactor/archiv... a while back on some toy projects; it makes some interesting reading and had me building a few similar functions around my own conventions and rules. On a larger scale, I would be sorely tempted to build something like that into the Model database and insist on a clean output as part of the approval criteria.