Mysterious Conversion problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Mysterious Conversion problem

Author

Topic

horse
Starting Member

5 Posts

Posted - 2005-01-20 : 08:19:08

I have moved existing code and database to a new server which had been working fine but now I get a conversion error.

I am passing a string : -

exec spJobs @clientContactID=9999,@est_datetime='20/11/2004 13:11:11'

to a sp

CREATE procedure dbo.spJobs

@clientContactID int,
@client_ref varchar(50) = '%',
@title varchar(500) = '%',
@est_datetime datetime ='01-jan-1980',
@est_id int = null
as

begin

if @est_id is null
begin

select status_desc, title, client_ref,(cl_contact_fname + ' ' + cl_contact_lname) as contact_name,
est_datetime,e.est_id from
tblEstimate e,tblEstStatus es,tblclientcontact cc
where e.status_id=es.status_id and cc.cl_contact_id=cc_id
and
(cc_id=@clientContactID or cc_id in
(select cl_contact_id_2
from tblClientContactViewContact
where cl_contact_id_1=@clientContactID)
)
and
e.status_id in (1,5)

and client_ref like '%' + @client_ref + '%'
and title like '%' + @title + '%'

and (est_datetime >= @est_datetime or est_datetime is NULL)

order by est_datetime

end

else

begin

select status_desc, title, client_ref,(cl_contact_fname + ' ' + cl_contact_lname) as contact_name,
est_datetime,e.est_id from
tblEstimate e,tblEstStatus es,tblclientcontact cc
where e.status_id=es.status_id and cc.cl_contact_id=cc_id
and
(cc_id=@clientContactID or cc_id in
(select cl_contact_id_2
from tblClientContactViewContact
where cl_contact_id_1=@clientContactID)
)
and
e.status_id in (1,5)

and client_ref like '%' + @client_ref + '%'
and title like '%' + @title + '%'

and (est_datetime >= @est_datetime or est_datetime is NULL)
and e.est_id = @est_id

order by est_datetime
end
end

Now like I said this had been working fine on the old server but I get the error on the new server : -

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

Have I overlooked something when setting up sql server on the new server as I can't quite understand why this isn't working now particularly as I haven't changed anything in the scripts

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-20 : 08:29:49

There's probably a regional setting on the server that's interpreting the date in U.S. (mm/dd/yyyy) format instead of European (dd/mm/yyyy) format. You can either change the region, or use SET DATEFORMAT DMY in all your SQL batches. Another option is to always pass dates in ISO format (yyyymmdd), this will always be interpreted correctly.

horse
Starting Member

5 Posts

Posted - 2005-01-20 : 09:38:56

Thanks for the response.

The regional settings are the same on both servers.

Anything else you can think of that it may be?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-20 : 14:27:12

horse;

read carefully these two recent threads:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44720
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44746

btw, in e.g. VB regional settings really do matter;

I must write CDate("31.01.04") else I get an error;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources