none
RDLC 2010 - Data conversion error RRS feed

  • Question

  • Good morning,

    The following SQL statement runs fine when run in Sql Server 2012, but when I run it through a RDLC 2010 engine I get the error msg below.  I'm thinking it's an issue with how the Convert statements are working with the date fields.

    I would appreciate any suggestions as to how I can get the SQL statement to run within MS Visual Studio.

    Here are the fld types for each one:

    psv.applydate     datetime w/length of 8

    wtke.startdate     datetime w/length of 8

    wtke.enddate     datetime w/length of 8

    psv.mgrsignoffthrudtm     datetime w/length of 8

    psv.prevpayperiodend     datetime w/length of 8

    SQL Statement

    SELECT  psv.empno, pe.fullnm, wtke.startdate, (wtke.enddate - 1) as enddate, psv.ee_type, sum(psv.time_amount) as time_amount,
      psv.earncode, psv.week, psv.tcs_orglvl_code_3,
      sum(case psv.week  when '01' then  psv.time_amount  else 0 end) as Week1Amt,
      sum(case psv.week  when '02' then  psv.time_amount  else 0 end) as Week2Amt
    FROM  tcsiface.dbo.PAYROLL_SUMMARY_V psv, person pe, mywtkemployee wtke
    WHERE  psv.empno = pe.personnum
      and pe.personid = wtke.personid
      and (CONVERT(DATETIME, CONVERT(DATE, psv.applydate)) between CONVERT(DATETIME, CONVERT(DATE, wtke.startdate)) and CONVERT(DATETIME, CONVERT(DATE, wtke.enddate)) - 1)
      and 'Y' = (
         CASE
          WHEN (:EmpSel = 'Y') THEN (
                CASE
                 WHEN CONVERT(DATETIME, CONVERT(DATE ,psv.mgrsignoffthrudtm)) >= CONVERT(DATETIME, CONVERT(DATE ,psv.prevpayperiodend)) THEN 'Y'
                 ELSE 'N'
                END
                )
          ELSE 'Y'
         END
        )
      and wtke.sessionid = :wtksessionid
    GROUP BY psv.empno, pe.fullnm, wtke.startdate, wtke.enddate, psv.ee_type,
      psv.earncode, psv.week, psv.tcs_orglvl_code_3
    ORDER BY psv.tcs_orglvl_code_3,psv.empno, psv.earncode

    Error Msg

    SQL Source: .Net SqlClient Data Provider , Message: Conversion failed when converting date and/or time from character string.

        Source: Kronos.Reporting.DBUtil
        Stack Trace:
           at Kronos.Workforce.CommonApp.Reporting.Framework.Utils.DBUtil.SQLDBAccessor.GetDatasetFromQuery(String SQLCommandText)
           at Kronos.Workforce.CommonApp.Reporting.Business.ProduceRDLCReport.RDLDataSourcePopulator.ExecuteRDLCQuery(RDLDataSetDef dsd)
           at Kronos.Workforce.CommonApp.Reporting.Business.ProduceRDLCReport.RDLDataSourcePopulator.ProcessRDLCQueries()
           at Kronos.Workforce.CommonApp.Reporting.Business.ProduceRDLCReport.ReportProcessor.ExportReport()
        Inner Exception:
            Type: System.Data.SqlClient.SqlException
            Message: Conversion failed when converting date and/or time from character string.
            Source: .Net SqlClient Data Provider

     

    • Moved by 宝宝徐 Monday, August 7, 2017 2:57 AM
    Thursday, August 3, 2017 2:08 PM

All replies

  • Hi friend,

    As you use RDLC file, have you installed SSRS in your VS?

    As I know three ways run the sql statement in VS:

    1. Put a report into SSRS and use the ReportViewer control to call SSRS to run your report
    2. Add the .RDL or .RDLC file into your project and create some objects to house the data so the report has an interface to the database
    3. Just run it like SSRS does, but without installing the SSRS server.

    which one are you using?

    if it's the third-one, you may refer to below to get the detailed configuration.

    https://www.codeproject.com/Articles/607382/Running-an-RDLC-SQL-Report-in-ASP-NET-without-SSRS

    there is the particular forum for SQL issues in MSDN, you may post your issue, the engineer there will offer you more professional answer.

    Thanks for your understandings.

     

    Best regards,

    Fletcher


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Friday, August 4, 2017 6:27 AM
  • Hi Fletcher,

    I'm new to the MSDN forums so thank you for pointing out the SQL forum.

    I'm actually running the RDL via an application called Kronos Wfc.

    Tanks.

    Scott

    Friday, August 4, 2017 1:18 PM