SET DATEFORMAT problems
1) SET DATEFORMAT has no effect on the actual storage of the table. It is only used to format output and interpret strings. Behind the scenes, all the date formats are stored as integers in a canonical format.

The actual representation of the data type depends on which type is used. For example, SMALLDATETIME is an integer that stores the number of seconds since 1900-01-01. Some of this bubbles to the surface when you cast like this:

SELECT CAST(0 AS SMALLDATETIME)


Result: 1900-01-01 00:00:00

Similarly, for DATETIME:

SELECT CAST(0 AS DATETIME)


Result: 1900-01-01 00:00:00.000

Interestingly, this fails:

SELECT CAST(0 AS DATE)


With exception: Explicit conversion from data type int to date is not allowed.

As should be obvious, something isn't quite fleshed out with the new types.

2) This limitation is documented here: http://msdn.microsoft.com/en-gb/library/ms189491.aspx


  The DATEFORMAT ydm is not supported for date, datetime2 and
  datetimeoffset data types.


Why is this so? Inside the database engine DATE, DATETIME2 and DATETIMEOFFSET take different code paths than DATETIME and SMALLDATETIME (you can validate this with a profiler). Because the new date formats arrived in the SQL Server code base long after DATETIME/SMALLDATETIME - different programmers worked on it and hence, some functionality is not 100% aligned (yet). Another example: In early versions of SQL Server 2008R2, bulk loading a DATE was much slower than bulk loading a SMALLDATETIME, even though one is smaller than the other. This was fixed before release. It's the kind of thing that happens in a big organisation with lots of coders.

3) This means that it is not possible for you to know if the date format is valid until you actually try it. In your case, it means that even if you do an "show query plan" without execution, you will not know that there is an error in the format until you actually execute the query.