Home Dashboard Directory Help
Search

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


Status: 

Active


84
3
Sign in
to vote
Type: Suggestion
ID: 290971
Opened: 8/3/2007 2:59:04 PM
Access Restriction: Public
Duplicates: 536154
0
Workaround(s)
view

Description

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
Sign in to post a comment.
Posted by Robert Heinig II on 6/10/2014 at 6:59 AM
Seven years later. ydm is still the *default* format for some european locales, even though none of the natives around is actually using it for anything but satire. That said, IMHO impact is fairly low, as best practice should be to never install localized MS software except on end-user clients.
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."
Sign in to post a workaround.