I tried with 1,2 and 3 quotes and with/without conversion, but I am getting
Error: Conversion failed when converting the varchar value
Can someone please help me?
with conversion
I tried with 1,2 and 3 quotes and with/without conversion, but I am getting
>Error: Conversion failed when converting the varchar value
Can someone please help me?
with conversion
inner join xxx.xxx.PITM'+@Datemm_yyyy+' pitm
on nep.s_pitm_c=pitm.pitm_c
and cast(year(s_tran_d) as nchar)='''+@yyyy+'''
and cast(month(s_tran_d) as nchar) ='''+@mm+'''
inner join xxx.xxx.PITM'+@Datemm_yyyy1+' pitm1
on nep.s_pitm_c=pitm1.pitm_c
and cast(year(dateadd(m,-1,s_tran_d)) as nchar) =''+ @yyyy1 +''
and cast(month(s_tran_d)-1 as nchar)= '''+@mm1 +'''
without conversion
without conversion
inner join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
and year(s_tran_d) =''+@yyyy+'' and month(s_tran_d) =''+@mm+''
inner join xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
and year(dateadd(m,-1,s_tran_d)) =''+ @yyyy1 +'' and month(s_tran_d)-1= ''+@mm1 +''
I also tried by keeping 1 or 2 or 3 single quotes.
When I put 2 quotes I am getting
> Error: Conversion failed when converting the varchar value '+@yyyy+' to data type int.
Full code:
/***********************************************************************************************************************************************/
/***********************************************************************************************************************************************/
--ANALYST : RAMU
--JOB PURPOSE : FIND CUSTOMERS WHO ARE PAYING ERA FEE AND FIND WHETHER THEY ARE ON NON EQUAL PAYMENSTS(FOCIS)
--DATE RANGE : POUPLATION BETWEEN 2018-10-01 to 2019-08-31
--INPUT DATASETS :
/* ]*/
--OUTPUT DATASETS : BISANDPIT_SDI..RK_CUSTOMERS_PAID_ERA_FEE
/***********************************************************************************************************************************************/
/***********************************************************************************************************************************************/
-------------------------------------------
--GET ALL CUSTOMERS WHO PAID ERA
-------------------------------------------
drop table if exists ##loan_account_details_focis
select
nep.s_pitm_c,
nep.s_tran_d,
pitm.LOAN_CNSN_STRT_D loancategory_start_date,
pitm1.LOAN_CNSN_STRT_D lastmonth_loan_catgory_start,
pitm.TFPR_D transfer_pricing_date
into ##loan_account_details_focis
from BISANDPIT_CUSTOMERREM.DBO.RK_NEP_PROBABLE_POOL nep
left join focis.focis.PITM02_2018 pitm on nep.s_pitm_c=pitm.pitm_c
and year(s_tran_d)=2018 and month(s_tran_d)=02
left join focis.focis.PITM01_2018 pitm1 on nep.s_pitm_c=pitm1.pitm_c
and year(s_tran_d)=2018 and month(s_tran_d)=01
where 1=2
When I put 2 quotes I am getting
Error: Conversion failed when converting the varchar value '+@yyyy+' to data type int.
Full code:
Declare @sql nvarchar(max)
Declare @StartDate Date;
Declare @EndDate Date;
Declare @Datemm_yyyy Char(7);
Declare @StartDate1 Date;
Declare @EndDate1 Date;
Declare @Datemm_yyyy1 Char(7);
declare @yyyy int;
declare @yyyy1 int;
declare @mm int;
declare @mm1 int;
Set @StartDate='20050501'
Set @EndDate=EOMonth(@StartDate);
Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')
Set @StartDate1='20050401'
Set @EndDate1=EOMonth(@StartDate1);
Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
set @yyyy=year(@StartDate)
set @mm=month(@StartDate)
set @yyyy1=year(@StartDate1)
set @mm1=month(@StartDate1)
print @StartDate
print @EndDate
print @Datemm_yyyy
print @StartDate
print @EndDate
print @Datemm_yyyy
print @yyyy
print @yyyy
print @mm
print @mm1
While @StartDate<'20181101'
Begin
Set @sql='
insert into ##loan_account_details_focis
select
nep.s_pitm_c,
nep.s_tran_d,
pitm.LOAN_CNSN_STRT_D loancategory_start_date,
pitm1.LOAN_CNSN_STRT_D lastmonth_loan_catgory_start,
pitm.TFPR_D transfer_pricing_date
from
xxxxBISANDPIT_CUSTOMERREM.DBO.RK_NEP_PROBABLE_POOL nep
inner join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
and cast(year(s_tran_d) as varchar(10)nchar)=''+@yyyy+''=@yyyy
and cast(month(s_tran_d) as varchar(10)nchar) =''+@mm+''=@mm
inner join xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
and cast(year(dateadd(m,-1,s_tran_d)) as varchar(10)nchar) =''+@yyyy1+''=@yyyy1 and cast(month(s_tran_d)-1 as varchar(10)nchar)= ''+@mm1+''@mm1
'
exec (@sql)
print @sql
Set @StartDate=DateAdd(Month,1,@StartDate)
Set @EndDate=EOMonth(@StartDate);
Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')
Set @StartDate1=DateAdd(Month,1,@StartDate1)
Set @EndDate1=EOMonth(@StartDate1);
Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
set @yyyy=year(@StartDate)
set @yyyy1=year(@StartDate1)
set @mm=month(@StartDate)
set @mm1=month(@StartDate1)
--print @startDate
End
;
select * from ##loan_account_details_focis
