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

0
votes

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
4
sqlsql-serverdatetimesql-server-2008-r2dynamic-sql
Microsoft SQL Server 2008 R2 : Version - 10.50.2500.0 - user2191477
Thank you. Please always tag your questions with the version, this prevents us from having to guess and ensures we use a solution that is most appropriate for your version. It isn't always relevant, but it's relevant often enough that I probably have to ask more often than I don't. - Aaron Bertrand

4 Answers

1
votes

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)+''''
0
votes

The literal

'03/01/1999'

is a string literal. You are converting document_Date to a varchar, then doing an inequality comparison

and convert (nvarchar,document_Date,101) > '+@fromdate

Since the dates are not formatted YYYYMMDD, the comparison is unlikely to produce the comparison you expect.

If @fromdate for example is '04/01/1900', it will be greater than '03/01/1999' because '04' is greater than '03'.

Instead, convert @fromdate to the same type as document_Date, e.g.

AND document_Date > convert(datetime, @fromdate)
0
votes

If the column document_date is already a datetime, why not declare declare @fromdate and @todate as datetime as well, then your code will be simpler and you won't have to do any conversion. It seems to me the conversion is unnecessary for what you are trying to do.

0
votes

Problem #1

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

Why are you defining these as Unicode strings, never mind strings without a length? Why are you using an ambiguous, unreliable format for the literal? Is that March 1st or January 3rd? Are you sure your instance of SQL Server agrees with you?

Problem #2

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

Why are you converting your column to a Unicode string, never mind a string without a length? Why are you comparing it as a string? You know that '05/05/2010' is greater than '02/21/2015', when compared as a string, right?

Solution

Stop using ambiguous formats. Stop thinking that dates are strings and should be compared as such.

How about:

DECLARE @fromdate DATETIME = '19990301'; -- guessing you meant March 1st. 
      -- See why ambiguous formats are bad and you should never use them?

...
SELECT @sql += ' AND document_Date > @fromdate;';

EXEC sp_executesql @sql, N'@fromdate DATETIME', @fromdate;

Now you don't have to do any conversions at all.