Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: 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

    James

  2. #2
    Join Date
    Aug 2003
    Posts
    111
    the 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).

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    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!
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •