I have created a store procedure which gives me the following error.
"
Error converting data type nvarchar to datetime.
"
i am using the following querry.
ALTER procedure [dbo].[pay_res_report]
@fd datetime,
@ld datetime ,
@id nvarchar(20) ,
@s nvarchar(20)
as
begin
declare @payable nvarchar(100)
declare @resable nvarchar(100)
declare @payable_adj float
declare @resable_adj float
declare @name nvarchar(100)
declare @op_balance float
declare @paid float
declare @received float
declare @Main_Opening_Balance float
declare @count int
set @count = 0
declare @coun int
if @s = 'c'
begin
set @name = ( select CUS_NAME from TBL_CUSTOMER where CUS_CODE = @id)
set @op_balance = ( select cast( CUS_CR_LIMIT as float) as ob from TBL_CUSTOMER where CUS_CODE = @id)
set @paid = isnull((select sum(cast( PR_AMOUNT as float)) as paid from PR where PR_TYPE = 'Paid' and cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer'),0)
set @received = isnull((select sum( cast( PR_AMOUNT as float)) as paid from PR where PR_TYPE = 'Received' and cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer'),0)
set @payable = ( select isnull( sum( cast(PI_TOTAL_AMOUNT as float) ) , 0) from SPI_MAIN_INFORMATION where CAST(PI_DATE AS DATETIME) < @fd and PI_CUSTOMER_ID = @id )
set @resable = (select isnull(sum( cast(SI_TOTAL_AMOUNT as float) ),0) from SSI_MAIN_INFORMATION where CAST(SI_DATE AS DATETIME) < @fd and SI_CUSTOMER_ID = @id )
set @payable_adj = ( select isnull( sum( cast(PI_TOTAL_AMOUNT as float) ) , 0) from SPI_MAIN_INFORMATION where CAST(PI_DATE AS DATETIME) >= @fd and CAST(PI_DATE AS DATETIME) <= @ld and PI_CUSTOMER_ID = @id )
set @resable_adj = (select isnull(sum( cast(SI_TOTAL_AMOUNT as float) ),0) from SSI_MAIN_INFORMATION where CAST(SI_DATE AS DATETIME) >= @fd and CAST(SI_DATE AS DATETIME) <= @ld and SI_CUSTOMER_ID = @id )
set @Main_Opening_Balance = ((@op_balance + @resable + @paid) - (@payable -@received))
set @coun = ( select count(PR_CODE) as person from (
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,PR_AMOUNT as P ,'' as R from PR
where PR_TYPE = 'Paid' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0
union all
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,'' as P ,PR_AMOUNT as R from PR
where PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0
) b
)
if @coun =0
begin
select 'N/A' as PR_CODE ,'N/A' as PR_PERSON ,'N/A' as PR_PERSON_DESIGNATION ,'N/A' as PR_TYPE ,'N/A' as PR_DATE ,'N/A' as PR_PAY_MODE , cast (0 as float) as Paid , cast( 0 as float) as Rece , cast( 0 as float) as R , @name as Name,cast ( @Main_Opening_Balance as float) as ob , @payable_adj as adj_pay,@resable_adj as adj_res
end
else
begin
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , cast (P as float) as Paid , cast( R as float) as Rece , cast( R as float) ,Name,cast ( OB as float) as ob , @payable_adj as adj_pay,@resable_adj as adj_res from
(
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,PR_AMOUNT as P ,'' as R , @name as Name , @Main_Opening_Balance as OB from PR
where PR_TYPE = 'Paid' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0
union all
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,'' as P ,PR_AMOUNT as R , @name as Name , @Main_Opening_Balance as OB from PR
where PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0
)a
end
end
if @s = 's'
begin
set @name = ( select SUP_NAME from TBL_SUPPLIER where SUP_CODE = @id)
set @op_balance = (select cast(SUP_EMAIL as float) as ob from TBL_SUPPLIER where SUP_CODE = @id)
set @paid = isnull((select sum(cast( PR_AMOUNT as float)) as paid from PR where PR_TYPE = 'Paid' and cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'),0)
set @received = isnull((select sum(cast( PR_AMOUNT as float)) as paid from PR where PR_TYPE = 'Received' and cast( PR_DATE as datetime) < @fd and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'),0)
set @Main_Opening_Balance = ((@op_balance + @received) - @paid )
set @coun = ( select count(PR_CODE) as person from (
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,PR_AMOUNT as P ,'' as R from PR
where PR_TYPE = 'Paid' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0
union all
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,'' as P ,PR_AMOUNT as R from PR
where PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Customer' and CAST(PR_AMOUNT AS FLOAT) >0
) b
)
if @coun =0
begin
select 'N/A' as PR_CODE ,'N/A' as PR_PERSON ,'N/A' as PR_PERSON_DESIGNATION ,'N/A' as PR_TYPE ,'N/A' as PR_DATE ,'N/A' as PR_PAY_MODE , cast (0 as float) as Paid , cast( 0 as float) as Rece , cast( 0 as float) as R , @name as Name,cast ( @Main_Opening_Balance as float) as ob
end
else
begin
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , cast (P as float) as Paid , cast( R as float) as Rece , cast( R as float) ,Name,cast ( OB as float) as ob from
(
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,PR_AMOUNT as P ,'' as R , @name as Name , @Main_Opening_Balance as OB from PR
where PR_TYPE = 'Paid' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'
union all
select PR_CODE , PR_PERSON , PR_PERSON_DESIGNATION , PR_TYPE ,PR_DATE ,PR_PAY_MODE , PR_AMOUNT ,'' as P ,PR_AMOUNT as R , @name as Name , @Main_Opening_Balance as OB from PR
where PR_TYPE = 'Received' and (cast( PR_DATE as datetime) <= @ld and cast( PR_DATE as datetime) >= @fd) and PR_PERSON = @id and PR_PERSON_DESIGNATION = 'Supplier'
)a
end
end
how can i remove this error. plzen help me!!!!