Deprecate the date literal interpretation 'YYYY-DD-MM' except in compatibility modes <= 90

Deprecate the date literal interpretation 'YYYY-DD-MM' except in compatibility modes <= 90 by Steve Kass

Active

Type:	Suggestion
ID:	290971
Opened:	8/3/2007 2:59:04 PM
Access Restriction:	Public
Duplicates:	536154

Workaround(s)

view

The new types [date], [datetime2], and [datetimeoffset] interpret the date literal 'YYYY-AA-BB' as 'year-month-day' regardless of regional or dateformat setting. This is great.

The problem is that [datetime] and [smalldatetime] have always behaved differently, and it appears that unfortunate behavior will be preserved (to provide backward compatibility).

This has the potential to create a lot of confusion. More people will adopt 'YYYY-MM-DD' as a safe format (which it should be, and which it is for the new types), so more people will accidentally use it where it is not safe: [datetime] and [smalldatetime].

I suggest eliminating the 'YYYY-DD-MM' interpretation altogether in Katmai, including for the existing types, and allowing it only for compatibility settings 90 and earlier--if at all.

Details (expand)

English

SQL Engine

Make 'YYYY-AA-BB' represent a date unambiguously across the entire product. In other words, change the way implicit conversion to datetime and smalldatetime works for strings in this specific format. This would have an effect in regions such as Europe where the standard date format is day-month-year. But the effect would be minimal, because even in these regions, day does not precede month in the interpretation of 'year-AA-BB', and most likely no one uses the string literal format 'year-day-month' intentionally.

Faster Development

Improved Reliability

Improved Administration

Improved Security

Other (please provides details below)

Improved internal (XML, date/time types) and external compatibility.

0 attachments

Posted by Steve Kass on 8/23/2008 at 5:27 PM

This suggestion was closed as "Fixed", but it is not fixed. I have reopened the item.

I suggested eliminating the 'YYYY-DD-MM' interpretation altogether in Katmai, including for the existing types, and allowing it only for compatibility settings 90 and earlier--if at all.

Unfortunately, the 'YYYY-DD-MM' interpretation was not eliminated altogether in Katmai as suggested. This interpretation remains at all compatibility levels for the pre-2008 date/time types.

(I will file a separate Connect bug for a change in behavior that may have confounded this issue.)

Posted by AKuz on 1/6/2008 at 10:14 AM

makes perfect sense to me.

Posted by Microsoft on 12/13/2007 at 9:39 AM

We're still considering whether we should deprecating the 'YYYY-DD-MM' for old datetime/smalldatetime under compat-mode 100 in Katmai. We'll keep you updated when the final decision is made.

thanks-michael

Posted by Microsoft on 8/10/2007 at 2:49 PM

Thanks for the great feedbacks.

In SQL Server 2008, we specifically make the 'YYYY-MM-DD' format for new date/time types independent to language or dateformat setting mainly based on the following reasons:
1. Standard alignment and strong customer requests
2. No cultural that actually uses the 'YYYY-DD-MM' format
3. No change for existing datetime/smalldatetime to avoid breaking changes to existing apps
4. It's acceptable for data type upgrade (i.e. to datetime2) to require possible app changes

Based on the feedbacks, there seems less concerns about #3 but more on #4 and the consistency between new date/time types and old ones.

We'll look over it more carefully by giving more research and investigation, and make sure we'll make the best choice for our customers.

Posted by aaronbertrand on 8/9/2007 at 8:38 PM

Wondering if you noticed message_id 9808:

"This session's YDM date format is not supported when converting from this character string format to date, time, datetime2 or datetimeoffset. Change the session's date format or provide a style to the explicit conversion."