Error converting data type nvarchar to datetime in SQL SSRS 2008

  • Hi All

    I created a report in SSRS 2008, the proc runs fine in query analyzer also from query inside the dataset. When i preview the report returns the error (Error converting data type nvarchar to datetime)

    I've also used an expresion to convert that particular field to =CStr(Fields!RECVDDATE.Value) but still i get the error above. See the code below. Any Idea

    CREATE PROCEDURE [dbo].[TESTCALL]

    (@start_date DATETIME = '2009-10-01 00:00', @end_date DATETIME = '2009-10-31')

    AS

    SELECT

    CALLLOG.CALLID,

    CAST(CALLLOG.RecvdDate + ' ' + CALLLOG.RecvdTime as DATETIME)AS RECVDDATE,

    CAST(CALLLOG.TENDERCLOSEDATE + ' ' + CALLLOG.TENDERCLOSETIME as DATETIME) AS TENDERCLOSEDATE, CALLLOG.CALLSTATUS,

    CALLLOG.TENDERNO,

    SUBSET.CSCOMPANY,

    CALLLOG.CALLDESC,

    DETAIL.COMMENTS,

    DETAIL.WORKREGION,

    SUBSET.LCSP,

    DETAIL.LCSPCLIENT,

    DETAIL.LEADCLIENTSERVICEPARTNER,

    DETAIL.LCSPPROVINCE,

    DETAIL.PERSUITTEAM,

    CALLLOG.LEADSUMMARY,

    CALLLOG.CLOSEDESC,

    DETAIL.COMPETENCY1,

    DETAIL.NEWCOMPETANCY,

    DETAIL.INDUSTRY,

    DETAIL.SERVICEAREA1,

    DETAIL.TOTALOPPVALUE,

    DETAIL.DELOITTEVALUE,

    DETAIL.BEEVALUE,

    DETAIL.ALLIANCEPARTNERVALUE,

    DETAIL.NOVALDESC,

    DETAIL.BEEPARTNERS,

    DETAIL.OTHERALLIANCEPARTNERS,

    DETAIL.REPORTINGFISCALYEAR,

    CALLLOG.BRIEFINGDATE,

    CALLLOG.WONDATE,

    CALLLOG.LOSTDATE,

    CALLLOG.SMARTNET,

    DATENAME(YY, CALLLOG.RECVDDATE) + ' ' + DATENAME(MM, CALLLOG.RECVDDATE) AS [MONTH]

    FROM CALLLOG INNER JOIN DETAIL

    ON CALLLOG.CALLID = DETAIL.CALLID

    JOIN SUBSET

    ON SUBSET.CallID = CALLLOG.CALLID

    WHERE (CALLLOG.RecvdDate) >= @start_date

    AND (CALLLOG.RecvdDate) <= @end_date

    AND DETAIL.SERVICEAREA1 IN ('RFT','RFP','EOI','Database','RFQ','RFI','RFQualification')

    ORDER BY CALLLOG.CALLID

    It's better to fail while trying, rather than fail without trying!!!

  • try this in your where statement

    WHERE (CALLLOG.RecvdDate) >= cast(@start_date as datetime)

    AND (CALLLOG.RecvdDate) <= cast(@end_date as datetime)

  • Hi

    Still get the same error

    Ta!!

    It's better to fail while trying, rather than fail without trying!!!

  • i did have this some time ago, it was the date settings on the server.

    it didnt look at '/' as a seperator only a '-'.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply