Solved

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

Posted on 2014-04-10
10
8,040 Views
Last Modified: 2014-04-14
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
Comment
Question by:sqldba2013
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39991003
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
 
LVL 16

Assisted Solution

by:Kamal Khaleefa
Kamal Khaleefa earned 125 total points
ID: 39991004
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
 

Author Comment

by:sqldba2013
ID: 39991053
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 33

Expert Comment

by:ste5an
ID: 39991123
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
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39991127
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
 

Author Comment

by:sqldba2013
ID: 39991175
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
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 250 total points
ID: 39991192
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 39992192
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
 

Author Comment

by:sqldba2013
ID: 39999345
Thanks to all for your help on this.

Increasing points from 250 to 500.
0
 

Author Closing Comment

by:sqldba2013
ID: 39999365
--
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question