March 11, 2007 at 4:09 am
Hello all.
Though a lot of messages concerning date formats have been posted on different threads, some issues have still not been claryfied.
Example: executing DBCC USEROPTIONS in QA reveals my language setting as being 'us_english' and my dateformat as being 'mdy'.
SELECT DATENAME(dd,'07/01/03') + ' ' + DATENAME(mm,'07/01/03') + ' ' + DATENAME(yyyy,'07/01/03') -> results in: 1 July 2003
What would you expect for '2007/01/03' ?
Clearly, 'mdy' will not work. It's not too complicated to figure out, SQL-Server will interpret any 4-digit-number as being the year, but the following digits? It could be March 1st as well as January 3rd.
Both possible formats, that is: 'ydm' and 'ymd' are perfectly legal. (SET DATEFORMAT ...) and will indeed deliver the above results respectively. Both are different from the active setting 'mdy'
But what will happen without explicitely setting the dateformat to one of the two?
The server decides to return January 3rd. Why? There must be an internal (undocumented?) precedence in the interpretation of date-strings WHEN THEY DO NOT MATCH the default (or - per connection - specifically assigned) dateformat setting.
[It's arguably no feature. It reminds me of Excel ... in it's dumb behaviour trying to interpret anything leaving you with postal addresses like 'Main Street November 3rd' which in the source flat file originally read 'Main Street 3-11']
In my opinion this is a very dangerous behaviour and an error is the result I'd prefer in such a case.
Now, the real question to me is: how can you find out IN ADVANCE, what will happen to non-compliant date-strings passed by an application of any kind?
-------
Second question: is there a way to find out what the active dateformat setting for a connection is? the way you can query for the datefirst setting? (SELECT @@DATEFIRST?)
(SELECT @@language is clearly no answer, since the DATEFORMAT setting can be changed independently.)
----
Dates are the first issue we should leave behind when moving towards mars ... 😉
_/_/_/ paramind _/_/_/
March 12, 2007 at 7:07 am
When your language setting is us_english, SQL Server accepts dates in two formats: MDY and YMD. YMD is a "universal" format which accepted regardless of the language setting. That is a very good thing, because if you use the YMD exclusively, you never have to worry about the language setting.
If your case, when you execute
SELECT DATENAME(dd,'07/01/03') + ' ' + DATENAME(mm,'07/01/03') + '
' + DATENAME(yyyy,'07/01/03')
SQL Server will assume MDY format. However, by using a four digit year (which I recommend you always use), SQL Server detects the year first, and uses YMD format.
I don't see any problems or ambiguity with this. It works as expected.
March 13, 2007 at 1:44 am
I agree with mkeast. Stick to yyyy first specially when your start to hit client non english but server english, etc.
March 13, 2007 at 7:59 am
I would expect January 3, 2007. SQL Server's default is YMD, so given 2007/01/03, SQL Server 'knows' the first part has to be the year and that means the rest will be resolved based on the default.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply