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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.