Error converting data type nvarchar to datetime

I have a known working report that I added a parameter and "if" statement to. Since adding, I now receive an error when previewing the report in SSRS: "Error converting data type nvarchar to datetime". The parameter I recently added is char, not datetime or nvarchar so I am not clear if this error is related to the newly added parameter. I can parse or run the query without an issue if I set the parameters in SSMS. There are two locations I have edited since the query's last known working state, they are notated in the query. Thoughts?

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LRP_ContributionDetail_BASE_trn]    Script Date: 03/10/2015 10:31:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER                   PROCEDURE [dbo].[LRP_ContributionDetail_BASE_trn](
									@fyear int= null,
									@fyear_end int = null,
									@start_dt_post datetime= null,
									@end_dt_post datetime=null,
	 @list_no int = 0,
	 @start_dt datetime = null,
	 @end_dt datetime = null,
	 @include_restricted char (1) = null --recently added parameter
	 )

AS

SET ANSI_WARNINGS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON


if @fyear_end is null or @fyear_end = 0 set @fyear_end = @fyear

if @start_dt_post is null set @start_dt_post = '1/1/1900'
if @start_dt is null set @start_dt = '1/1/1900'

if @end_dt_post is null set @end_dt_post = GETDATE()
if @end_dt is null set @end_dt = GETDATE()


SELECT	CONT.ref_no As Contribution_ID,
		CONT.customer_no,
		CUST.lname,
		CUST.fname,
		CAT.id As Cat_ID,
		CAT.description AS Cat_Desc,
		CAMP.fyear As FiscalYear,
		CAMP.campaign_no As Camp_ID,
		CAMP.description AS Camp_Desc,
		FUND.fund_no As Fund_ID,
		FUND.description AS Fund_Desc,
		CDES.id As Desig_ID,
		CDES.description As Desig_Desc,
		CONT.cont_type,
		CONT.cont_dt,
		cont.cont_amt,
		CONT.recd_amt,
		(cont.cont_amt-CONT.recd_amt) AS Balance,
		TRN_AMT = SUM(x.trn_amt), --show actual transactions
		post_no = 0,--BAT.post_no,
		MORG.memb_org_no as MembType_ID,
		MORG.description as MembType_Desc,
		fund.nonrestricted_income_gl_no
into #work
FROM	T_TRANSACTION x
join	T_CONTRIBUTION cont on x.ref_no = cont.ref_no
		INNER JOIN T_CAMPAIGN CAMP ON x.campaign_no=CAMP.campaign_no
		INNER JOIN TR_CAMPAIGN_CATEGORY CAT ON CAMP.category=CAT.id
		INNER JOIN T_CUSTOMER CUST ON CONT.customer_no=CUST.customer_no
		join T_BATCH bat on x.batch_no = bat.batch_no
		join T_FUND fund on x.fund_no = fund.fund_no
		LEFT OUTER JOIN TR_CONT_DESIGNATION CDES ON CONT.cont_designation=CDES.id
		LEFT OUTER JOIN T_MEMB_ORG MORG ON CAMP.memb_org_no=MORG.memb_org_no
WHERE	
x.trn_type in (1,2,5,4) --gift/pledge
and (@fyear is null or camp.fyear between @fyear and @fyear_end)
and cont.cont_dt between @start_dt and @end_dt --contribution date
 and bat.posted_dt between isnull(@start_dt_post,'1/1/1900') and isnull(@end_dt_post,getdate()) and --posting date
 (Coalesce(@list_no, 0) = 0
		or Exists (Select * From [dbo].T_LIST_CONTENTS 
					Where customer_no = cont.customer_no
					and list_no = @list_no))
and cont.cont_amt > 0
--and cont.ref_no = 549924
GROUP BY 
cONT.ref_no,CONT.customer_no,CUST.lname,CUST.fname,CAT.id,CAT.description,
CAMP.fyear,CAMP.campaign_no,CAMP.description,FUND.fund_no,FUND.description,
CDES.id,CDES.description,CONT.cont_type,CONT.cont_dt,CONT.recd_amt,
MORG.memb_org_no,MORG.description, cont.cont_amt, fund.nonrestricted_income_gl_no

if @include_restricted = 'N' --recently added if statement
	begin
		--find where resticted activity takes place
		select b.ref_no, b.trn_type, trn_amt = case when b.trn_type = 11 then SUM(b.trn_amt)* (-1)
			else SUM(b.trn_amt) end
		into #rest
		from #work a
		join t_transaction b on a.Contribution_ID = b.ref_no
		where b.trn_type in (10,11)
		group by b.ref_no, b.trn_type

		select ref_no, rest_amt = SUM(trn_amt)
		into #rest_sum
		from #rest
		group by ref_no

		--delete where money is still restricted
		delete #work
		from #rest_sum a
		where a.ref_no = #work.Contribution_ID
		and a.rest_amt = #work.cont_amt

		--only show unresricted amount
		update #work
		set cont_amt = cont_amt - a.rest_amt,
		recd_amt = recd_amt - a.rest_amt
		from #rest_sum a
		where a.ref_no = #work.Contribution_ID
		and a.rest_amt > 0

		update #work
		set Balance = cont_amt - recd_amt
	end

select * from #work 
				
EXEC LUP_Log_ProcedureCall @ObjectID = @@PROCID;

Open in new window

rwaterzAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
I suspect it's the parameter values coming in causing this error.  What line# do you get?  If you can find the error line#, and it's zero, then it is the input params causing the error.
0
 
rwaterzAuthor Commented:
Hi - Unfortunately SSRS isn't providing the line# of the error. When I click "error list" toward the bottom left of SSRS, that does not give any more detail.
0
 
rwaterzAuthor Commented:
Parmeters
0
 
rwaterzAuthor Commented:
I was able to get this working. In the "dataset properties" box in Visual Studio I was executing the sproc and listing the parameters. I removed the new @include_restricted parameter from here, and set a default value in the sproc like this:

   
 if @include_restricted is null set @include_restricted = 'N'

Open in new window

Also changed the tail of the sproc to include login for wether @include_restricted was a yes or no instead of just a no. Here is the revised working sproc:
        USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LRP_ContributionDetail_BASE_trn]    Script Date: 03/10/2015 10:31:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO






ALTER                   PROCEDURE [dbo].[LRP_ContributionDetail_BASE_trn](
                                    @fyear int= null,
                                    @fyear_end int = null,
                                    @start_dt_post datetime= null,
                                    @end_dt_post datetime=null,
     @list_no int = 0,
     @start_dt datetime = null,
     @end_dt datetime = null
     ,@include_restricted char (1) = null
     )

AS

SET ANSI_WARNINGS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON


if @fyear_end is null or @fyear_end = 0 set @fyear_end = @fyear

if @start_dt_post is null set @start_dt_post = '19000101'
if @start_dt is null set @start_dt = '19000101'

if @end_dt_post is null set @end_dt_post = GETDATE()
if @end_dt is null set @end_dt = GETDATE()

if @include_restricted is null set @include_restricted = 'N'

SELECT  CONT.ref_no As Contribution_ID,
        CONT.customer_no,
        CUST.lname,
        CUST.fname,
        CAT.id As Cat_ID,
        CAT.description AS Cat_Desc,
        CAMP.fyear As FiscalYear,
        CAMP.campaign_no As Camp_ID,
        CAMP.description AS Camp_Desc,
        FUND.fund_no As Fund_ID,
        FUND.description AS Fund_Desc,
        CDES.id As Desig_ID,
        CDES.description As Desig_Desc,
        CONT.cont_type,
        CONT.cont_dt,
        cont.cont_amt,
        CONT.recd_amt,
        (cont.cont_amt-CONT.recd_amt) AS Balance,
        TRN_AMT = SUM(x.trn_amt), --show actual transactions
        post_no = 0,--BAT.post_no,
        MORG.memb_org_no as MembType_ID,
        MORG.description as MembType_Desc,
        fund.nonrestricted_income_gl_no
into #work
FROM    T_TRANSACTION x
join    T_CONTRIBUTION cont on x.ref_no = cont.ref_no
        INNER JOIN T_CAMPAIGN CAMP ON x.campaign_no=CAMP.campaign_no
        INNER JOIN TR_CAMPAIGN_CATEGORY CAT ON CAMP.category=CAT.id
        INNER JOIN T_CUSTOMER CUST ON CONT.customer_no=CUST.customer_no
        join T_BATCH bat on x.batch_no = bat.batch_no
        join T_FUND fund on x.fund_no = fund.fund_no
        LEFT OUTER JOIN TR_CONT_DESIGNATION CDES ON CONT.cont_designation=CDES.id
        LEFT OUTER JOIN T_MEMB_ORG MORG ON CAMP.memb_org_no=MORG.memb_org_no
WHERE   
x.trn_type in (1,2,5,4) --gift/pledge
and (@fyear is null or camp.fyear between @fyear and @fyear_end)
and cont.cont_dt between @start_dt and @end_dt --contribution date
 and bat.posted_dt between isnull(@start_dt_post,'19000101') and isnull(@end_dt_post,getdate()) and --posting date
 (Coalesce(@list_no, 0) = 0
        or Exists (Select * From [dbo].T_LIST_CONTENTS 
                    Where customer_no = cont.customer_no
                    and list_no = @list_no))
and cont.cont_amt > 0
--and cont.ref_no = 549924
GROUP BY 
cONT.ref_no,CONT.customer_no,CUST.lname,CUST.fname,CAT.id,CAT.description,
CAMP.fyear,CAMP.campaign_no,CAMP.description,FUND.fund_no,FUND.description,
CDES.id,CDES.description,CONT.cont_type,CONT.cont_dt,CONT.recd_amt,
MORG.memb_org_no,MORG.description, cont.cont_amt, fund.nonrestricted_income_gl_no

if @include_restricted = 'Y'
    select * from #work

if @include_restricted = 'N' 
    begin
        --find where resticted activity takes place
        select b.ref_no, b.trn_type, trn_amt = case when b.trn_type = 11 then SUM(b.trn_amt)* (-1)
            else SUM(b.trn_amt) end
        into #rest
        from #work a
        join t_transaction b on a.Contribution_ID = b.ref_no
        where b.trn_type in (10,11)
        group by b.ref_no, b.trn_type

        select ref_no, rest_amt = SUM(trn_amt)
        into #rest_sum
        from #rest
        group by ref_no

        --delete where money is still restricted
        delete #work
        from #rest_sum a
        where a.ref_no = #work.Contribution_ID
        and a.rest_amt = #work.cont_amt

        --only show unresricted amount
        update #work
        set cont_amt = cont_amt - a.rest_amt,
        recd_amt = recd_amt - a.rest_amt
        from #rest_sum a
        where a.ref_no = #work.Contribution_ID
        and a.rest_amt > 0

        update #work
        set Balance = cont_amt - recd_amt

        select * from #work
    end 


EXEC LUP_Log_ProcedureCall @ObjectID = @@PROCID;

Open in new window

0
 
rwaterzAuthor Commented:
Lead me in the right direction
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.