SQL code explicit conversion error during execution

Question

I am getting error "Explicit conversion from data type int to date is not allowed." while trying to execute the code. The SQL is

USE [xxDB]
GO

declare @currentMonth int = 1,
@currentYear int = 2014,
@newExpireMonth int =6,
@newExpireYear int =2018,
@actualDate varchar(30),
@fromDate varchar(30),
@toDate varchar(30)


declare @Sql varchar(8000)


begin

create table #MytempTab1 
        (
            actualNewDate varchar(30), 
            newUserId int              
        )

  insert into #MytempTab1
   select 
    (RIGHT( '00' + CAST(ISNULL(ss.ExpiryMonth, '01') as varchar(2)), 
          2)+'-01'+'-'+RIGHT('0000' + CAST(ISNULL(ss.ExpiryYear, '2014') as 
          varchar(4)), 4)) as actualNewDate, ss.UserId as newUserId

    from
    dbo.UserProfile u
    inner join dbo.webpages_UsersInRoles wur on u.UserId=wur.UserId
    inner join dbo.SpreedlySubscriber ss on u.UserId=ss.UserId      

  where 
    (wur.RoleId=4) 

end


  set @Sql= '
 select
  ss.FirstName as FirstName, ss.LastName as LastName,   
  ss.ExpiryMonth as ExpMonth, ss.ExpiryYear as ExpYear 

from dbo.UserProfile u
inner join dbo.webpages_UsersInRoles wur on u.UserId=wur.UserId
inner join dbo.SpreedlySubscriber ss on u.UserId=ss.UserId
inner join #MytempTab1 mt on ss.UserId= mt.newUserId
inner join dbo.[Order] odr on ss.OrderId=odr.OrderId


where (wur.RoleId=4) and '


set @fromDate =  RIGHT('00' + CAST(@currentMonth  as varchar(2)), 2) +'-01'+'-'+ RIGHT('0000' + CAST(@currentYear as varchar(4)), 4)
set @toDate=  RIGHT('00' + CAST(@newExpireMonth as varchar(2)), 2) +'-01'+'-'+ RIGHT('0000' + CAST(@newExpireYear as varchar(4)), 4)

set @Sql= @Sql+ '(CONVERT(date, mt.actualNewDate, 110) BETWEEN CONVERT(date, '+@fromDate+', 110) AND CONVERT(date, '+@toDate+', 110))'

 print (@Sql)
 EXEC(@Sql)
 drop table #MytempTab1

I believe it is the last line which is comparing the dates. The temporary table only crating the string and this last line converting it to date and comparing.

Thanks in advance.

I would recommend not doing dynamic SQL like that inside a stored proc. Instead, use sp_executesql with explicit parameters - so you **know** you are safe from SQL Injection. https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters — mjwills, Jun 04 '18 at 06:55
`(CONVERT(date, mt.actualNewDate, 110) BETWEEN CONVERT(date, '+@fromDate+', 110) AND CONVERT(date, '+@toDate+', 110))` is a very inefficient way to confirm whether a date is between two dates. Change the column type of `mt.actualNewDate` to `Date` type - and also change @fromDate and @toDate to be `Date`. Then use `mt.actualNewDate BETWEEN @fromDate AND @toDate` (with `sp_executesql` parameters). — mjwills, Jun 04 '18 at 06:58

score 1 · Answer 1 · answered Jun 04 '18 at 06:49

1

You have a comma trailing in the select list:

  set @Sql= '
 select
  ss.FirstName as FirstName, ss.LastName as LastName,   
  ss.ExpiryMonth as ExpMonth, ss.ExpiryYear as ExpYear, 
--                                                    ^

answered Jun 04 '18 at 06:49

Christian.K

47,778
10
99
143

Hi, after removing the comma, the new error is telling "Explicit conversion from data type int to date is not allowed." – kh_sam Jun 04 '18 at 07:59
Hence, the comments to your question above; and, consider asking a new question. – Christian.K Jun 04 '18 at 08:46

SQL code explicit conversion error during execution

1 Answers1