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 :)
