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
left 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+'''
leftjoin 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
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:
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
xxx.DBO.RK_NEP_PROBABLE_POOL nep
left join xxx.xx.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
leftjoin xxx.xx.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
'
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
;
Best Answer
Look at the output from the print, and you can see the problem that the error is reporting:
here you are explicitly casting the result from
YEAR()
to a character format then comparing it to@yyyy
which is an integer. You do not need that case, you instead need to cast the variable as you make it part of the SQL. So instead ofyou want something like
The same problem exists elsewhere in your query too.
As an aside, when asking further questions try to create a smaller example that illustrates your issue. See How to create a Minimal, Complete, and Verifiable Example for database-related questions for more details on that.
For instance your example could easily be cut down to:
and that is still much larger than it could be cut down to while still illustrating the error. This will greatly increase your chances of getting useful answers to your questions.
(in other words: as much as is possible, help us to help you)