Converting to datetime
Converting to a datetime can be a dangerous game. If the month is specified as a word then the date format is unambiguous but very often the year, month and day are all numeric. In this case the conversion can easily be misinterpretted if done in the wrong way.
Until recently I'd thought that the YYYY-MM-DD date format was foolproof, regardless of regional differences:
SET @date = CAST('2003-04-01' AS DATETIME)
SET @date = '2003-04-01'
I discovered this week that this format is really no better than any other. A new user was created with the default language of their server login set to "British English" rather than "English", and the above conversion generated a date of 4th Jan rather than 1st Apr.
It seems that British English defaults to a date format of ydm, whereas English defaults to ymd. SET DATEFORMAT can be used to control this in some situations, but if you're doing a conversion in a function then this won't work.
A much better, and totally unambiguous, way of creating the date is to use CONVERT. I've used this for years when formatting datetime values as strings but never the other way round.
Using CONVERT it's possible to specify the exact format you're using. I've chosen 112 (YYYYMMDD) as the cleanest:
SET @date = CONVERT(DATETIME, '20030401', 112)