Have you ever wondered which date format SQL Server uses for default datetime parameter values ? Is it month-day-year, day-month-year, year-month-day ? Does it depend on SET options ?
As of Sql Server 2008, it's actually the language SET option of the active connection that is used when calling the stored procedure. It's not the language option at DDL time when the stored procedure was created. The following example illustrates this point.
use tempdbset language 'english' go
create procedure up_1 @dt datetime2 = '07-01-49' as select month(@dt) as 'month'go
set language 'french' go
We've been wanting to change this for some time though, and some users have occasionally hit this issue. Of course language settings are not always important to the parsing of a default parameter, but when the conversion from string to the parameter type can be ambiguous, it's best to not leave it to chance, and in the case of datetime, datetime2, datetimeoffset etc... language does matter during parsing.
Expressions are not allowed in stored procedure default parameters, so something likecreate procedure ... @dt datetime2 = convert(datetime2, '07-01-49', <picking the right style here>)won't work.
And until we come around to folding the constant at DDL time,I recommend you use the ISO-8601 date format when using datetime default parameters. It's the best way to be certain how the literal will be parsed at runtime.