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 tempdb
set language 'english'
go

create procedure up_1 @dt datetime2 = '07-01-49'
as
 select month(@dt) as 'month'
go


exec up_1
go

set language 'french'
go

exec up_1
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 like
create 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.