SQLRNNR (12/31/2013)

---

WayneS (12/31/2013)

---

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

You could also just fix the code as follows:

SET DATEFORMAT 'YMD'

declare @ThisDate SMALLDATETIME = '2013/12/31'

And to clarify a bit, it's the Instance collation and not just the database collation that will cause those issues Wayne mentioned.

It should also be noted that Wayne fixed the second option otherwise his error message would have been different. :-D:-D;-)