In Québec, we have to daily contend with two date formats mm-dd-yyyy (US English) and dd-mm-yyyy (French). I have repeatedly seen users confusing April 1 with January 4. So much that when I print a date column in a report I always add the date format below the column name in a table column's header.

I have found that the only format that cannot possibly be misunderstood by anyone is yyyy-mm-dd. But sometimes you have to live with the customers' own way of doing things.

And I use the hyphen instead of the slash because I find it more legible.

I do not even need all the fingers on one hand to count the authors I have occasionally corresponded with.

You definitely stand out among the crowd.

And still amazed a your total of 33,313 points acquired in what 10 ? 20 ? years.

Heh... and some of my posts are actually useful.

I started posting regularly on SQLServerCentral.com back in 2003 or so because they closed down my old favorite (Belution.com). I'm glad it happened because this is one of the best communities of people dedicated to the arts of SQLServer that I've ever seen. I thank Steve Jones and the other plank owners of this site every day.

I used to be the leading poster but Gail Shaw passed me up a couple of years ago and I've never been able to catch up. She's incredible.

Thank you very much for the feedback on this article. I really appreciate it.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T."--22 Aug 2013

I have been in IT most of past 42 years and am glad to still have some hair even though it is now gray/white. I was thinking this morning that IBM use to have that old "THINK" sign as a slogan. Contemplating that, I believe that what keeps us interested and "young" is that we continue learning. Your efforts to publish new and interesting things keeps us learning and it is appreciated.

Jeff, I'm glad this discussion was resurrected since I missed it the first time around. I too was not aware of the sys.syslanguages table! There have been a few occasions when I wanted to use SET LANGUAGE or SET DATEFORMAT in functions...but SQL won't let us do that.

The procedure below doesn't really address that directly, but while studying the syslanguages table I decided to write a short procedure that will set the date format based on the language ID. I figured I'd just toss it out and perhaps it can be useful to someone dealing with international dates.

If a date is entered, it will be returned in the designated language format. If no date is entered, the procedure will output the codes from the table so that SET DATEFORMAT and SET LANGUAGE can be instantiated outside of the procedure. An example script is below.

I think format() is part of the .Net stack, and it wouldn't surprise me if there is an overhead to calling out to .Net functions rather than using native SQL Server functions. However, with the built-in support for locales, it's still useful.

I will say in its favour is that it's identical syntax to formatting in reporting services, and I wouldn't be surprised if there's something the same in SSIS (bit rusty there). Kind of a blah benefit though.

I tend to use cast() where I can, then convert(), because cast() is portable across Oracle and SQL Server (the two platforms I work on) and they're also portable across versions of SQL Server. So format() is kind of cool, but the only time I've used it thus far I had to turn around and pull it back out so I could get the code running on SQL Server 2008.

Another related aside: using the date functions (like datename, datepart, dateadd, datediff, etc) resolves so many logic issues when converting between implicit date formats that it's just good practice to use them, irrespective of speed. This happens all the time in NZ -- the default installation date format is the us MM/DD/YYYY, but New Zealand date format is DD/MM/YYYY, and we often have code moving between the two locales.