Error converting data type nvarchar to datetime

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

Select allOpen in new window

        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;

Select allOpen in new window