Datetime conversion error in sql.

Question

0.00/5 (No votes)

See more:

I have a stored proc like :

SQL

create proc sp_trade
(
 @ID int,
 @TradeDate datetime
)

declare @query varchar(max)

set @query='select into table1
            where datediff(d,table1.colDate,@TradeDate)>0'

exec(@query)

It throws error 'conversion failed when converting datetime from character string' in sql server 2005.
I tried '+ CONVERT(VARCHAR(50), CAST(@TradeDate AS DATETIME), 101)+' but then datediff doesn't work since it is changed in varchar and datediff expects datefield.

Posted 17-Jul-14 1:08am

Member 10684823

Updated 17-Jul-14 1:17am

CHill60

v2

Add a Solution

Comments

[no name] 17-Jul-14 7:21am

Is table1.colDate a datetime? Why would you think that you would need to convert a datetime (@TradeDate) to a datetime?

Member 10684823 17-Jul-14 7:26am

yes table1.colDate is datetime.

I wrote in procedure:
set @query='select into table1
where datediff(d,table1.colDate,'+@TradeDate+')>0'

but it throws error 'conversion failed when converting datetime from character string'. I just want the proper syntax to pass date field in this sp.

[no name] 17-Jul-14 7:42am

Yes... I am not surprised at all. Now why would you think the string "'+@TradeDate+'" could be converted to a datetime? Read the documentation for the DateDiff function.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2014-07-17T01:27:00

I get completely different errors but here are some suggestions for fixing your problem

Firstly select into table1 is not syntactically correct - what are you selecting? You need a column list in there, or at worst *

Secondly - by the time you execute the query @TradeDate is no longer declared and you will get an error similar to

Quote:
Must declare the scalar variable "@TradeDate"

You are going to the trouble of generating dynamic sql so you need to insert the value passed into that query string

SQL

set @query='select into table1
            where datediff(d,table1.colDate,''' + @TradeDate + ''')>0'

or why bother with dynamic sql at all...

SQL

declare @query varchar(max)

select into table1
            where datediff(d,table1.colDate,@TradeDate) > 0
exec(@query)

Maciej Los · Answer 2 · 2014-07-17T01:29:00

On the first look, replace:

SQL

set @query='select into table1
            where datediff(d,table1.colDate,@TradeDate)>0'

with:

SQL

set @query='select Field1, Field2, ..., FieldN
into table1 
where datediff(d,table1.colDate,' + @TradeDate + ')>0'

On the other hand, why to build query as variable type string? Just exec statement as:

SQL

select Field1, Field2, ..., FieldN
into table1 
where datediff(d,table1.colDate, @TradeDate)>0

For further information, please see: INTO Clause[^]

Datetime conversion error in sql.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0