How Do I Resolve This Error Conversion Failed When Converting Date And/Or Time From Character String.

Question

1.00/5 (1 vote)

See more:

Hi ,
How do i select the Data's between the two date's

SQL

declare @fromdate Datetime,@todate datetime,@sql nvarchar(max) ;
select @fromdate ='2013/05/01'
select @todate='2014/05/31'
select @adv_id='4'
set @sql= N'';
set @sql += N' select *from table where paid_date between '+@fromdate+' and '+@todate+''
print @sql
EXEC sp_executesql @sql;

but it says

SQL

Conversion failed when converting date and/or time from character string.

Posted 18-Jun-14 2:35am

King Fisher

Updated 18-Jun-14 8:28am

v2

Add a Solution

1 solution

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)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-06-18T02:45:00

Solution 1

SQL

set @sql += N' select *from table where paid_date between '+@fromdate+' and '+@todate+''

This line forces SQL to convert between types (it contains different types: string and datetime). As you didn't specified how to do that convert SQL goes by the last parameter on the line, which is type of datetime! You have explicitly convert datetime variables to string...

SQL

set @sql += N' select *from table where paid_date between '''+convert(nvarchar, @fromdate, 103)+''' and '''+convert(nvarchar, @todate, 103)+''''

Also notice the quotes! It ensures that the date-strings created by CONVERT will be treated as string (enclosed by quotes)

Posted 18-Jun-14 2:45am

Kornfeld Eliyahu Peter

Comments

King Fisher 18-Jun-14 9:24am

thank you for Reply,
it says "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

and i changed into
select *from table where convert(nvarchar,paid_date,103) between '''+convert(nvarchar, @fromdate, 103)+''' and '''+convert(nvarchar, @todate, 103)+''''

but it doesn't filter any records ,it shows all those values from table. :(

my column datatype is Datetime.

Kornfeld Eliyahu Peter 18-Jun-14 9:28am

Probably 103 is not your code... Check help for CONVERT to pick the right number
(the number represents a datetime format)
By the way why to use dynamic sql? If you have not you need not - your life will be much better without...

King Fisher 18-Jun-14 9:32am

kind of task sir, :)

King Fisher 19-Jun-14 0:26am

i used 102 format. its worked.thanks :)

Kornfeld Eliyahu Peter 19-Jun-14 2:16am

Glad to help!

King Fisher 19-Jun-14 2:19am

:) :)