Since the QotD is all about learning, allow me to point out a few things:
First of all, none of these solutions really work...
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 8
Must declare the scalar variable "@ThisDate".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@ThisDate".
(when run on a case-sensitive collation, so change them all to be as defined: @thisdate)
And our European friends, using the YDM format, will get this:
Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
To have this code work right, just use the ISO 8601 standard for dates/times:
select @thisdate = '2013-12-31T00:00:00' -- dates with times - the milliseconds are optional
--or
select @thisdate = '20131231' -- just dates
The advantage in using the ISO 8601 format is that it is an international standard with unambiguous specification. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE setting.
Ref: datetime
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes