Conversion failed when converting the varchar value '09/11/2013' to data type int

What are you trying to do with that?

You have used dynamic SQL for no apparent reason. You seem to be converting a datetime input to a varchar and then asking if a given that varchar is between 2 other datetimes that have been converted to varchars. Also, there is no WHERE clause in the sql.

The above will produce this:

SELECT [Submitted Date] FROM temp and convert(VARCHAR(10),[Submitted Date],101) between mm/dd/yyyy and mm/dd/yyyy

Select allOpen in new window

Also, what if the date_from and date_to are null?

Post your printed value.

Thanks for your help on this issue and find herewith updated query.

I have done changes as per your suggestion and I am facing same error

declare @Date_From datetime
declare @Date_To datetime
set @Date_From='01/01/2010'
set @Date_To='01/01/2014'

declare @sqlquery varchar(8000)
set @sqlquery=''
			set @sqlquery=@sqlquery+'SELECT [Submitted Date]'
			set @sqlquery=@sqlquery+'FROM temp  where 1=1'
			
			IF @Date_From<>'' and @Date_To<>''
				BEGIN
					set @sqlquery=@sqlquery+' and convert(VARCHAR(10),[Submitted Date],20) between '+convert(VARCHAR(10),@Date_From,20)+' and '+convert(VARCHAR(10),@Date_To,20)+' '
				END	
				
			ELSE
				BEGIN
					set @sqlquery=@sqlquery+''
				END
				print @sqlquery
				exec(@sqlquery)			

Select allOpen in new window

Error: Conversion failed when converting the varchar value '09/11/2013' to data type int.

Use sp_executesql. E.g.

DECLARE @Statement NVARCHAR(MAX);
DECLARE @From DATETIME;
DECLARE @To DATETIME;

-- Use a non-ambiguous formt.
SET @From = '2014-04-01';
SET @To = '2014-04-07';

SET @Statement = '
SELECT	database_name,
	backup_finish_date
FROM	msdb.dbo.backupset 
WHERE	( 1 = 1 )
';
			
SET @Statement = @Statement + ' AND backup_finish_date BETWEEN @from AND @to';

EXECUTE sp_executesql @Statement, N'@from DATETIME, @to DATETIME', @from = @From, @to = @To;

Select allOpen in new window

You missed my point of what I needed. I needed the output from this

print @sqlquery

Never mind. Read your output. Your latest one produces this:

SELECT [Submitted Date]FROM temp  where 1=1 and convert(VARCHAR(10),[Submitted Date],20) between 2010-01-01 and 2014-01-01

This will never work. Try running it.

Hi Lee,

Plaese find the query with requested output.

Output of print @sqlquery:

SELECT [TAQOS Submitted Date]FROM #temp  where 1=1 and convert(VARCHAR(10),[TAQOS Submitted Date],101) between 01/01/2010 and 01/01/2014
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '09/11/2013' to data type int.

IF object_id('tempdb..#temp') IS NOT NULL
BEGIN

  DROP TABLE #temp

END

CREATE TABLE #temp
(

	[TAQOS Submitted Date] DATETIME,
	[Inquiry Number] NVARCHAR(255),
	[CPO Number] VARCHAR(50)
)

INSERT INTO #temp
(
	[Inquiry Number],[CPO Number]
)
SELECT distinct PRA.inq_num,[PO Number]
FROM REACT.dbo.ps_requests_asr PRA with(nolock) left join [BCAMDB].dbo.CPOA with(nolock) ON cast(Inquiries as NVARCHAR(450)) = '[' + PRA.inq_num + ']' 
WHERE PRA.inq_num is not null

UPDATE #temp SET
[TAQOS Submitted Date]=PRA.create_date 
FROM #temp WITH(NOLOCK) INNER JOIN dbo.ps_requests_asr PRA with(nolock) ON #temp.[Inquiry Number] = PRA.inq_num

declare @Date_From datetime
declare @Date_To datetime
set @Date_From='01/01/2010'
set @Date_To='01/01/2014'

declare @sqlquery varchar(8000)
set @sqlquery=''
			set @sqlquery=@sqlquery+'SELECT [TAQOS Submitted Date]'
			set @sqlquery=@sqlquery+'FROM #temp  where 1=1'
			
			IF @Date_From<>'' and @Date_To<>''
				BEGIN
					set @sqlquery=@sqlquery+' and convert(VARCHAR(10),[TAQOS Submitted Date],101) between '+convert(VARCHAR(10),@Date_From,101)+' and '+convert(VARCHAR(10),@Date_To,101)+' '
				END	
				
			ELSE
				BEGIN
					set @sqlquery=@sqlquery+''
				END
				print @sqlquery
				exec(@sqlquery)			

Select allOpen in new window

Thanks to all for your help on this.

Increasing points from 250 to 500.

--