Solved

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

Posted on 2014-04-10
10
7,664 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:sqldba2013
Comment Utility
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
Comment Utility
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:ScottPletcher
ScottPletcher earned 125 total points
Comment Utility
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
Comment Utility
Thanks to all for your help on this.

Increasing points from 250 to 500.
0
 

Author Closing Comment

by:sqldba2013
Comment Utility
--
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

762 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

10 Experts available now in Live!

Get 1:1 Help Now