Sql – Conversion failed when converting the nvarchar value ’06/30/2012′ to data type int

datetimedynamic-sqlsqlsql serversql-server-2008-r2

I'm having issues trying to set two variables, a From Date, and a To Date. Once I solve the issue for one, the other will be easy enough to figure out. I'm getting the error:

Conversion failed when converting the nvarchar value '06/30/2012' to data type int.

It's worth noting that I'm using a view to bring various tables together, and then executing the ones I want via sp_executesql. When I print my query I can see that it's recognizing the @fromdate as 0, perhaps that's the issue..

 declare @fromdate nvarchar
 declare @todate nvarchar
 select @fromdate = '03/01/1999'
 select @todate = ''

 IF @fromdate <> ''
BEGIN
    SELECT @sql = @sql + ' and convert (nvarchar,document_Date,101) > '+@fromdate+''
END

 if @todate <> ''
BEGIN
    SELECT @sql = @sql + ' and convert(nvarchar,document_date,101) >  '+@todate+''
END

The column document_date is datetime. Any ideas? If so, could you explain why this is occurring so I can learn and avoid it happening in the future? I've included the print SQL below.

 SELECT  [system_status]      
  ,[document_status_code]      
  ,[rpt_category]      
  ,[category]      
  ,[user_status_cd]      
  ,[user_status]      
  ,[assigned_to_id]      
  ,[assigned]      
  ,[assigned_to_date]      
  ,[owner_id]      
  ,[owner]      
  ,[rpt_acct_code]      
  ,[acct_name]      
  ,[rpt_title]      
  ,[job_name]      
  ,[logged_time]      
  ,[rpt_format_type]      
  ,[rpt_run_id]      
  ,[rpt_doc_id]      
  ,[rpt_id]      
  ,[rpt_filename]      
  ,[rpt_file_path]      
  ,[rpt_run_name]      
  ,[sla_start_date]      
  ,[sla_due_date]      
  ,[sla_completed_date]      
  ,[sla_status]      
  ,[SLA_Days]
  ,[Document_Date] 
  FROM VW_Document_Main
  WHERE 1=1 and convert (nvarchar,document_Date,101) > 0 
  order by document_status_code  ,owner_id

Best Answer

Part of the problem is that you do not have a length on your declaration of @fromdate

It is only returning the first character because you do not have a length. The declaration should say:

declare @fromdate nvarchar(10)
declare @todate nvarchar(10)

Since you do not have a length, it is returning 0 which is the first character.

Secondly, you are also comparing string values of the dates, you should be comparing the date values. 

declare @fromdate datetime
declare @todate datetime

Then your code could be:

SELECT @sql = @sql + ' and document_Date > '''+convert(varchar(10), @fromdate, 101)+''''
Related Topic