I wasn't sure what the confusion was here. All of the values in the example SELECT...INTO are constants. When you create a constant, the format you express it in tells SQL Server what data type to use. It's that simple.

Then, I read Microsoft's documentation on constants here: http://msdn.microsoft.com/en-us/library/ms179899.aspx .

If you value your sanity DO NOT READ MICROSOFT'S DOCUMENTATION ON CONSTANTS. They manage to get it COMPLETELY WRONG. This is the worst Microsoft documentation I have seen to date.

It starts with this: "The format of a constant depends on the data type of the value it represents." That's completely back-to-front. You create the data type and express it in a format first, then SQL Server interprets what it is based on the format you provide. The correct statement is "The data type of a constant is based on the format passed to SQL Server." It then goes on to provide several data type examples, including a lot of "date" constants which are actually all varchar constants.

What this means for this question is that '15 Jan 2013', being formatted as a varchar, is interpreted by SQL Server as a varchar. It will be treated as varchar by SQL Server until you tell SQL Server to treat it as something else, either by an explicit CAST/CONVERT or by assigning it to an expression already typed as something else.

It's that simple.