November 2, 2009 at 2:08 am
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!!!
November 2, 2009 at 2:16 am
try this in your where statement
WHERE (CALLLOG.RecvdDate) >= cast(@start_date as datetime)
AND (CALLLOG.RecvdDate) <= cast(@end_date as datetime)
November 2, 2009 at 2:39 am
Hi
Still get the same error
Ta!!
It's better to fail while trying, rather than fail without trying!!!
November 2, 2009 at 2:45 am
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