Why does SQL Server convert VARCHAR to DATETIME using an invalid style?

Question

I can't make out from the documentation why SQL Server parses a text in a format other than the specified style.

Regardless of whether I provide text in the expected format:

SELECT CONVERT(DATETIME, N'20150601', 112)

or incorrect format (for style 113):

SELECT CONVERT(DATETIME, N'20150601', 113)

The results are the same: 2015-06-01 00:00:00.000 I would expect the latter to fail to convert the date (correctly).

What rules does it employ when trying to convert a VARCHAR to DATETIME? I.e. why does the latter (incorrect format style) still correctly parse the date?

EDIT: It seems I've not been clear enough. Style 113 should expect dd mon yyyy hh:mi:ss:mmm(24h) but it happily converts values in the format yyyymmdd for some reason.

Answer 1

Because the date is in a canonical format ie(20150101). The database engine falls over it implicitly. This is a compatibility feature.

If you swapped these around to UK or US date formats, you would receive conversion errors, because they cannot be implicitly converted.

EDIT: You could actually tell it to convert it to a pig, and it would still implicitly convert it to date time:

select convert(datetime,'20150425',99999999)
select convert(datetime,'20150425',100)
select convert(datetime,'20150425',113)
select convert(datetime,'20150425',010)
select convert(datetime,'20150425',8008135)
select convert(datetime,'20150425',000)

And proof of concept that this is a compatibility feature:

select convert(datetime2,'20150425',99999999)

Although you can still implicitly convert datetime2 objects, but the style must be in the scope of the conversion chart.

Answer 2

Reason why is the date N'20150601' converted to valid datetime is because of fact that literal N'20150601' is universal notation of datetime in SQL Server. That means, if you state datetime value in format N'yyyymmdd', SQL Server know that it is universal datetime format and know how to read it, in which order.