Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Location
    Locked in side a tiny room with a keyboard and many monitors glowing away in the dark...
    Posts
    25

    Unanswered: Conversion Error

    I am working in SQL Server 2005 Reporting Services, and am getting a conversion error:
    "Conversion failed when converting datetime from character string"

    This is the query:

    SELECT dataid, hazclass, jurisdictions, hazardlist, incidentdate, regteamsresponding, hazmat_off, txtOther, globalid, DATEPART(mm, entrydate) AS month, DATEPART(yy, entrydate) AS year
    FROM dbo.table_215
    WHERE (globalid IS NOT NULL) AND entrydate IN(@month, 'month') AND entrydate IN(@year, 'year')
    ORDER BY dataid

    parameter: month
    SELECT DISTINCT DATEPART(mm, entrydate) AS month
    FROM dbo.table_215
    ORDER BY month

    parameter: year
    SELECT DISTINCT DATEPART(yy, entrydate) AS year
    FROM dbo.table_215
    ORDER BY year

    If I drop one of the parameters I get a similar error. But need to have both for the report. Any ideas Gurus? I'm new to this and haven't figured out all the quirks or I have too many and SSRS doesn't, ummmm.....

    Thanks!!!!!
    Attached Thumbnails Attached Thumbnails ConversionError.jpg  
    Torchwood
    "understand, in a sick and twisted sort of way?"

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is EntryDate defined as datetime, or varchar in the table table_215?

  3. #3
    Join Date
    Jul 2009
    Location
    Locked in side a tiny room with a keyboard and many monitors glowing away in the dark...
    Posts
    25
    It is datetime.
    Torchwood
    "understand, in a sick and twisted sort of way?"

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I think I see the problem. If you ran this on SQL Server (without reporting services) this line should throw an error:
    Code:
    entrydate IN(@month, 'month') AND entrydate IN(@year, 'year')
    Looks like you want something more like
    Code:
    datepart(mm, entrydate) = @month and datepart(yy, entrydate) = @year

Tags for this Thread

Posting Permissions

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