If only MSSQL Server wasn't broken in the way it interprets that if your user locale is set to UK English (give it a string formatted that way in other locales and the behaviour is as expected, in "English (British)" it sees the format as yyyy-dd-mm).
I'm British, I've worked with a large number of clients' SQL Server installs. I think I've only twice seem yyyy-dd-mm as a default behaviour, yyyy-mm-dd was far more common.
I've only seen it assume yyyy-dd-mm when the user's locale is set to "English (British)". In most instances I've come across this setting is just left at the default ("English", which implies "English (American)") even if everything else in the stack is localised for the UK, so the oddity isn't noticed. My build instructions now explicitly state that "English (British)" should not be used due to the way it affects how dates in strings are interpreted when the format NNNN-NN-NN is seen.
From memory, the safest format to use is actually yyyymmdd (without the hyphens) though, which is apparently supposed to be interpreted unambiguously by SQL Server.
I have a formatDate() function lying around somewhere, but I cannot create functions or views on many of the DBs. convert(varchar(8), @myDate, 112) seems a daft way to go about things - and I have been wondering if I have been doing something wrong...
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
If only MSSQL Server wasn't broken in the way it interprets that if your user locale is set to UK English (give it a string formatted that way in other locales and the behaviour is as expected, in "English (British)" it sees the format as yyyy-dd-mm).