Until recently i've been sure that there are 2 date formats that are completly locale insensitive.

Those 2 i thought were locale insensitive are yyyy-mm-dd HH:mm:ss.fff and yyyymmdd HH:mm:ss.fff.

And i was proven wrong a few days ago!

The only locale insensitive date format is yyyymmdd HH:mm:ss.fff

you can easily test this with this script:

DECLARE @t1 table (date DATETIME)
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '2007-10-22 10:15:3.83' )
– this fails
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '2007-22-10 10:15:3.83' )
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '20071022 10:15:3.83' )
SET DATEFORMAT ydm
INSERT INTO @t1 ( date )
VALUES ( '20071022 10:15:3.83' )
SELECT   *
FROM     @t1

Of course this is irrelevant with proper use of parameters, but that's not the point of the post :)