• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9364
  • Last Modified:

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

Hi All,

Please advise how to reslove below mentinoed data type conversion error.

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

Query:
declare @Date_From datetime
declare @Date_To datetime

declare @sqlquery varchar(8000)
set @sqlquery=''
set @sqlquery=@sqlquery+'SELECT [Submitted Date] FROM temp'

IF @Date_From<>'' and @Date_To<>''
BEGIN
	set @sqlquery=@sqlquery+' and convert(VARCHAR(10),[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)

Open in new window

0
sqldba2013
Asked:
sqldba2013
3 Solutions
 
Lee SavidgeCommented:
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

Open in new window


Also, what if the date_from and date_to are null?

Post your printed value.
0
 
Kamal KhaleefaInformation Security SpecialistCommented:
try to change the following
set @sqlquery=@sqlquery+' and convert(VARCHAR(10),[Submitted Date],101) between '+convert(VARCHAR(10),@Date_From,101)+' and '+convert(VARCHAR(10),@Date_To,101)+' '
 


To

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
0
 
sqldba2013Author Commented:
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)			

Open in new window


Error: Conversion failed when converting the varchar value '09/11/2013' to data type int.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ste5anSenior DeveloperCommented:
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;

Open in new window

0
 
Lee SavidgeCommented:
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.
0
 
sqldba2013Author Commented:
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)			

Open in new window

0
 
Lee SavidgeCommented:
This query isn't going to work:

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 

Open in new window


it needs to be this:

SELECT [TAQOS Submitted Date] FROM #temp  where 1=1 and convert(VARCHAR(10),[TAQOS Submitted Date],101) between cast('01/01/2010' as datetime) and cast('01/01/2014' as datetime)

Open in new window


Which means your code that produces the dynamic SQL needs to alter to this:

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 cast('''+convert(VARCHAR(10),@Date_From,101)+''' as datetime) and cast('''+convert(VARCHAR(10),@Date_To,101)+''' as datetime) '
				END	
				
			ELSE
				BEGIN
					set @sqlquery=@sqlquery+''
				END
				print @sqlquery
				exec(@sqlquery)			

Open in new window

0
 
Scott PletcherSenior DBACommented:
Constant date values are literals in SQL Server, that is, they require quotes around them.

More importantly, you should never convert a table column to match a constant, do it the other way around.  And for dates specifically, you should use >= and <, not "between".

Putting that into practice, the best way to do this is:


IF @Date_From<>'' and @Date_To<>''
BEGIN
      set @sqlquery=@sqlquery+' and ([Submitted Date] >= ''' + convert(VARCHAR(8),@Date_From,112) + ''' and [Submitted Date] < ''' + convert(VARCHAR(8),DATEADD(DAY, 1, @Date_To),112) + ''') '
END
0
 
sqldba2013Author Commented:
Thanks to all for your help on this.

Increasing points from 250 to 500.
0
 
sqldba2013Author Commented:
--
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now