I have a problem with a stored procedure. The stored procedure is
called as follows:

exec InterfacePatientUpdate
@HospNo=N'D509929',@NHSNo=N'',@SName=N'MOHAMOUD',@ FName=N'Ahmed',@Sex=N'M',@DOB='1989-10-19
10:10:10',@Location=N'0ORALT',@BloodType=N'UNKNOWN '

When I run the procedure on one SQL Server 2005 machine, it works
fine, but when I run it on another machine, I get the error…

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1989-10-19 10:10:10'.

However, if I remove the time, (so that DOB is just
@DOB=’1989-10-19’), then it works. The date/time is in the standard
format, so why does it work on one SQL Server 2005 machine and fails
on another SQL Server 2005 machine?

The procs and database are identical.

Actually the datetime is not in a standard format. Since this wrapped it is
hard to say if there was a CR in there to begin with or just from the format
of the post. It appears to me there is no space between the date and time.
In any case drop the dashes and see if this works?
,@DOB='19891019 10:10:10'

http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/colu...qldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"JaffaB" <jaffa_brown@yahoo.co.uk> wrote in message
news:72ffbfd2-20cc-46a4-9de6-c3b01fe0d7a8@t54g2000hsg.googlegroups.com...
I have a problem with a stored procedure. The stored procedure is
called as follows:

exec InterfacePatientUpdate
@HospNo=N'D509929',@NHSNo=N'',@SName=N'MOHAMOUD',@ FName=N'Ahmed',@Sex=N'M',@DOB='1989-10-19
10:10:10',@Location=N'0ORALT',@BloodType=N'UNKNOWN '

When I run the procedure on one SQL Server 2005 machine, it works
fine, but when I run it on another machine, I get the error…

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1989-10-19 10:10:10'.

However, if I remove the time, (so that DOB is just
@DOB=’1989-10-19’), then it works. The date/time is in the standard
format, so why does it work on one SQL Server 2005 machine and fails
on another SQL Server 2005 machine?

The procs and database are identical.

>> @DOB=’1989-10-19’), then it works.

>> The date/time is in the standard format,

What standard format? The safest format to use is YYYY-MM-DDTHH:MM:SS ...
notice the T, it's actually important. But I don't think the date format is
the problem, otherwise you would get an error other than a syntax error,
e.g.:

Msg 296, Level 16, State 3, Line 2
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.

or

Msg 295, Level 16, State 3, Line 2
Conversion failed when converting character string to smalldatetime data
type.

>> @DOB=’1989-10-19’), then it works.

Are you using these smart apostrophes like ’ or do you really have correct
ones like ' ? Is it possible there is a CR/LF pair in an unlucky spot in
the middle of a string literal?

Sorry I meant: ' I may have to take a screen shot to get the point across,
but some of your quotes come over as closing single quotes (e.g. from Word)
and not precisely apostrophes. Are you sure the date literal lights up in
red in management studio, and there are no carriage returns?

>> @DOB='1989-10-19'),

On May 15, 3:35pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> >> @DOB=’1989-10-19’), then it works.
> >> The date/time is in the standard format,
>
> What standard format? The safest format to use is YYYY-MM-DDTHH:MM:SS ....
> notice the T, it's actually important. But I don't think the date format is
> the problem, otherwise you would get an error other than a syntax error,
> e.g.:
>
> Msg 296, Level 16, State 3, Line 2
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
>
> or
>
> Msg 295, Level 16, State 3, Line 2
> Conversion failed when converting character string to smalldatetime data
> type.
>
> >> @DOB=’1989-10-19’), then it works.
>
> Are you using these smart apostrophes like ’ or do you really have correct
> ones like ' ? Is it possible there is a CR/LF pair in an unlucky spot in
> the middle of a string literal?

Thanks - I will try. But that does not answer the principle question
which is.. why does it work different on two machines both running SQL
Server 2005. If both failed, I would accept it, but when our
development machine does one thing, and the test does another - there
is something wrong.

> Thanks - I will try. But that does not answer the principle question
> which is.. why does it work different on two machines both running SQL
> Server 2005. If both failed, I would accept it, but when our
> development machine does one thing, and the test does another - there
> is something wrong.

My guess is that one of them is different. The string is different, the
literals are delimited differently, it is being pasted differently, etc.
Two different SQL Servers aren't going to interpret syntax differently
unless the syntax is actually different.

A

<<which is.. why does it work different on two machines both running SQL
Server 2005.>>

Nothing strange about this. How language dependent dateformat strings are interpreted depends on the
language of the login for the connection in question. So it is possible that the login you are using
on serverA has language X and the login you are using on server B has language Y. This is the main
reason why you want to us language INdependent datetime string format.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"JaffaB" <jaffa_brown@yahoo.co.uk> wrote in message
news:ea6d2897-0d05-46fe-a9f5-f3f690f85074@f63g2000hsf.googlegroups.com...
On May 15, 3:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> >> @DOB=’1989-10-19’), then it works.
> >> The date/time is in the standard format,
>
> What standard format? The safest format to use is YYYY-MM-DDTHH:MM:SS ...
> notice the T, it's actually important. But I don't think the date format is
> the problem, otherwise you would get an error other than a syntax error,
> e.g.:
>
> Msg 296, Level 16, State 3, Line 2
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
>
> or
>
> Msg 295, Level 16, State 3, Line 2
> Conversion failed when converting character string to smalldatetime data
> type.
>
> >> @DOB=’1989-10-19’), then it works.
>
> Are you using these smart apostrophes like ’ or do you really have correct
> ones like ' ? Is it possible there is a CR/LF pair in an unlucky spot in
> the middle of a string literal?

Thanks - I will try. But that does not answer the principle question
which is.. why does it work different on two machines both running SQL
Server 2005. If both failed, I would accept it, but when our
development machine does one thing, and the test does another - there
is something wrong.