As far as I'm aware, it's specified in the SQL standard - the following is
lifted from Joe Celko's "SQL For Smarties" book (recommended, btw):
In SQL, character strings are printable characters enclosed in single
quotation marks.
...
Double quotations marks are reserved for column names that have embedded
spaces or that are also SQL-reserved words.
Without the quotes, you're passing a number a bit larger than 20 million to
the stored procedure (which is left unconverted, even though it looks like a
date). With the quotes, SQL Server is clever enough to try to
convert the string to a date, so it does what you mean. You can, if you want, explicitly
control how the conversion gets done using CAST:
http://msdn2.microsoft.com/en-us/library/ms141704.aspx.
Hope that helps.