Conversion failed when converting datetime from character string

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi Expert,

I am uday satardekar,

I am fetching records using sql server 2005 (stored procedure).
There are many criteria in where condition.so i wrote dynamic sql query.

all the criteria working fine for me.but when i append date query in where condition

It shows error

Conversion failed when converting datetime from character string.

Below is my date query example ,

SQL

DECLARE
@m_todate datetime,
@m_fromdate datetime


SET @QUERY='SELECT * FROM companyinfo company'
    SET @whereField=' WHERE '
    
    SET @criteriaSelected=''
    SET @finalQuery=''

	SET @finalQuery=@QUERY + @whereField + @criteriaSelected + '  COMPANY.date > '+@m_fromdate+' and COMPANY.date <= '+@m_todate+ ORDER BY COMPANY.date DESC '


		EXEC(@finalQuery)

I am taking @m_fromdate and @m_todate value from user.

I have use convert option also but it not giving me right results. and i want results based on only date not using date and time.

Following query also not working

SET @finalQuery=@QUERY + @whereField + @criteriaSelected + '  COMPANY.date > ' + Convert(varchar(30), @m_fromdate,101 )+' and COMPANY.date <= '+Convert(varchar(30),@m_todate,101 )+' ORDER BY COMPANY.date DESC '

when i print this query using print statement,it prints results like

SELECT * FROM companyinfo company WHERE COMPANY.date > 01/01/2010 and COMPANY.date < 01/01/2014 ORDER BY COMPANY.date DESC

but when i copy this query and run again,it showing result.

but not showing results in stored procedure.

Please help me.

Thanks in advance.

Posted 24-Jan-12 19:52pm

udusat13

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)

Prasad_Kulkarni · Answer 1 · 2012-01-24T19:55:00

Solution 1

For your FromDate & ToDate parameters in code behind use this;

C#

fromDate = dtpFromDate.Value.Date;
toDate = dtpToDate.Value.Date.AddDays(1).AddSeconds(-1);

and, let me know what happened.

Posted 24-Jan-12 19:55pm

Prasad_Kulkarni

Updated 24-Jan-12 19:57pm

v2

Comments

udusat13 25-Jan-12 2:19am

Thanks.but

Its not working.
It not showing any results.

when i use date query without dynamic and using variable as datetime then it working for me.

But when i convert it into nvarchar and use it in dynamic sql statement.
then it not showing results.

SELECT * FROM companyinfo company WHERE COMPANY.date > 01/25/2012 ORDER BY COMPANY.date DESC
above query with date as nvarchar displays all the results from table,the rows less than date and greater than also.

But when i use same query with @m_fromdate as datetime then it working fine for me