Solved

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

Posted on 2014-04-10
10
7,805 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
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
 
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now