What date/time literal formats are LANGUAGE and DATEFORMAT safe?
In the documentation, it is very explicitly stated that the only safe formats are the ones I demonstrated at the very beginning of the question:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T 


However, it was recently brought to my attention that there is a third format that is equally immune to any language or dateformat settings:

yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator


TL;DR: This is true. For datetime and smalldatetime.

Read on for the longer version, and about as much proof as you're going to get.



There is a loophole that explains this - while the main text body fails to acknowledge yyyyMMdd hh:... as a format safe from transposed language or date format interpretations, there is a little blurb that says the date portion of such a string is not validated depending on dateformat settings:


  


It is rather unlike me to just take the documentation at its word, usually. You can say I'm a bit skeptical. And the language is ambiguous here, too - it just states that this is about the combination of date and time, not calling out the space explicitly (that could be a carriage return, for all I know). It also says that it isn't multi-language, which means it could fail in certain languages,  but we'll find out shortly that that is incorrect as well.

So I set out to prove that no combination of language/dateformat could make this specific format fail.

First, I created a little block of dynamic SQL for each language:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';


This produced 34 rows of output like this:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';    


I copied that output to a new query window, and above it, I generated this code, which would hopefully try to convert that same date (the 13th of March) to the 3rd day of the 13th month in at least one case:

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';


Nope, every language worked just find in ydm. I tried every other format, too, and also every date/time data type. 34 successful conversions to March 13th, every time.

So, I do concede to @AndriyM and @ErikE that, indeed, there is a 3rd safe format. I will keep this in mind for future posts, but I've pounded the drums about the other two in so many places, I'm not going to hunt them all down and correct them now.



By extension, you would think this one would be safe, but no:

yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator


I think in every language, this will yield the equivalent of:


  Msg 241, Level 16, State 1, Line 8
  Conversion failed when converting date and/or time from character string.




For completeness, there is a fourth safe format, but it is only safe for conversions to the newer date/time types (date, datetime2, datetimeoffset). In these cases the language settings can't interfere:

yyyy-MM-dd hh:mm:...


However, I highly recommend against its use because it only works for the newer types, and the old ones are still in a massive amount of use, in my experience. Why have the dashes there when everywhere else (or in fact in that same code, if the data type changes) you have to remove them?

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);


Even given the same source string, the conversions yielded quite different results:

März    Juli    März


The format that works for datetime (yyyyMMdd) will also always work for date and the other new types. So, IMHO, just always use that. And given the third format for types with date/time (yyyyMMdd hh:...), this will actually allow you to be more consistent - even if the date component is always a little bit less readable.



Now it will just take me a few years, give or take, to get into the habit of demonstrating the three safe formats when I'm talking about string representation of dates.