Data conversion error

Hi all

Thanx for all your enthusiastic participation yesterday. I've got a new question on data conversion related to the same problem i asked about yesterday.

I have a SQL query as follows which generates the following error when it is executed against the database using sql query analyser and also through an ASP.NET application.

Error (Query Analyser):
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

So it's basically this line
(Convert(datetime, Request.closeDate, 103) > Convert (datetime, '8/25/2003', 103))
that is causing the problem

SELECT Request.requestID,
Users.clientCode,
Job.jobID,
Job.allocatedTime,
Job.spentTime,
Request.state,
Job.deadline

FROM Request,
Users,
Job,
Staff

WHERE (
Users.userID = Request.userID AND
Job.staff = Staff.staffID AND
Job.request = Request.requestID AND
(
(Job.staffProgress != 1 AND Request.state = 'In Progress') OR
(Convert(datetime, Request.closeDate, 103) > Convert (datetime, '8/25/2003', 103))
) AND
Request.state = 43
)
ORDER BY
Job.deadline,
Job.allocatedTime,
Request.priority

The weird thing is that this query works fine on our live server but fails when i try to execute it on the local machine. I think the live server is running SQL Server Service Pack 3, and the one on my local machine, i couldn't find out for some reason.

Any suggestions would be greatly appreciated

Cheers

Jamesthe same piece of sql query above also generated the following error.

Syntax error converting the varchar value 'In Progress' to a column of data type int.

The datatype of Request.state is varchar(20).|||I should mention all the associated data types in the WHERE block.

Job.staff (decimal)
Staff.staffID (decimal)
Job.request (decimal)
Request.requestID (decimal)

Job.staffProgress (float)
Request.state (varchar(20))
Request.closeDate (datetime)

Please help

Thanx

James|||Originally posted by nano_electronix
The datatype of Request.state is varchar(20).

Look again for your data type of Request.State! In your WHERE clause, you are comparing Request.State both with 'In Progress' and with 43. One of these are wrong, and your error message is clearly stating, that the data type of Request.State is INT!