Nice question.

A useful reference that should perhaps have been in the explanation is the BOL datetime page which has a information about the ODBC timestamp literals with their literal_type fields such as the one - {d '2012-11-15'} - used in this QotD. All ODBC timestamp literals represent SQL datetime values, even when the literal_type specifies "date only" (as in today's example) or "time only". These are the only datetime literals recognised by T-SQL. As far as I know T-SQL recognises no date literals, no time literals, and no datetime2 literals, so it is not possible to create a column which has any of those types by using select into with literal constant values.