If you are stuck with an existing db design, you should be able to do something like this:

create function fnGetNextModelNo(@type nchar(1)) returns nvarchar(4) as

    begin

        declare @return nvarchar(4),

make that cast('20041010' as datetime)

Alternatively...

Don't convert '10/10/2004'!

cast('20040101' as datetime) will work every time.

The format is yyyymmdd and is unambiguous.

Simplest way I know is as follows:

cast(floor(cast(getdate() as float)) as datetime)

This will give the current date with no time.

Call me a geek, but I really think that this type of question misses the point, much as many questions in VB competency exams do:

What does is matter whether you...

As far as I am aware, any context changes (current DB, SET options etc) only apply for the duration of the dynamically executed batch.

So:

I've never experienced this problem, although I connect through OLEDB rather than ODBC.

The approach I tend to take is to either put the insert into a stored procedure eg:

May 11, 2004 at 4:58 pm