conversion faild when converting date time from charachter string error

Question

0.00/5 (No votes)

See more:

Hi guy when i run this Stored procedure it give me message error

conversion faild when converting date time from charachter sting

stored procedure as following

SQL

Create proc searchData

    @StartDate datetime, 
    @EndDate datetime

as
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery ='SELECT * from Employee Where (1=1)' 
  If (@StartDate is not NULL) AND (@EndDate is not NULL)
         Set @SQLQuery = @SQLQuery + ' And (JoinDate 
         BETWEEN '+ @StartDate +' AND '+@EndDate+')'
Exec (@SQLQuery)

JoinDate found in table Employee as datetime

but when i make stored procedure as following

it work in formate dd/mm/yyyy and this is what i need

SQL

ALTER  proc [dbo].[searchData]

@StartDate datetime 

@EndDate  datetime, 

as

select * from dbo.Employee e where JoinDate between @StartDate and @EndDate

Now what is the proplem in first stored procedure
Please help me if possible

Posted 31-Aug-14 21:03pm

ahmed_sa

Updated 1-Sep-14 5:01am

George Jonsson

v3

Add a Solution

Comments

ChauhanAjay 1-Sep-14 3:15am

Try this
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery ='SELECT * from Employee Where (1=1)'
If (@StartDate is not NULL) AND (@EndDate is not NULL)
Set @SQLQuery = @SQLQuery + ' And (JoinDate
BETWEEN '''+ @StartDate +''' AND '''+@EndDate+''')'
Exec (@SQLQuery)

George Jonsson 1-Sep-14 11:03am

How do you call the stored procedure?
What is your locale settings?

3 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)

Richard Deeming · Answer 1 · 2014-09-01T07:38:00

Use sp_executesql[^] instead of EXEC:

SQL

Declare @SQLQuery as nvarchar(2000);

SET @SQLQuery = N'SELECT * from Employee Where (1=1)';

If (@StartDate is not NULL) AND (@EndDate is not NULL)
    SET @SQLQuery = @SQLQuery + N' And (JoinDate Between @StartDate And @EndDate)';

EXEC sp_executesql @SQLQuery,
    N'@StartDate datetime, @EndDate datetime',
    @StartDate, @EndDate;

DineshMaind · Answer 2 · 2014-09-01T09:17:00

Make following changes in your procedure it will definitely work for you.

SQL

CREATE PROCEDURE searchData
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
	--@DemoDate is just place holder if JoinDate is null in table
	DECLARE @DemoDate DATETIME
	SET @DemoDate = GETDATE()

	SELECT * FROM Employee
	WHERE COALESCE(JoinDate, @DemoDate) 
	BETWEEN COALESCE(@StartDate, JoinDate, @DemoDate)
	AND COALESCE(@EndDate, JoinDate, @DemoDate)
END

Whether parameters @StartDate, @EndDate having null values or date values your procedure will work fine. If you are dam sure that JoinDate in table is always not null then following changes are sufficient.

SQL

CREATE PROCEDURE searchData
@StartDate DATETIME,
@EndDate DATETIME
AS
BEGIN
	SELECT * FROM Employee
	WHERE JoinDate
	BETWEEN COALESCE(@StartDate, JoinDate)
	AND COALESCE(@EndDate, JoinDate)
END

Any suggestions are acceptable.

ChauhanAjay · Answer 3 · 2014-09-03T16:59:00

Change your first procedure in the following format

SQL

Create proc searchData

    @StartDate datetime,
    @EndDate datetime

as
Declare @SQLQuery as nvarchar(2000)
SET @SQLQuery ='SELECT * from Employee Where (1=1)'
  If (@StartDate is not NULL) AND (@EndDate is not NULL)
         Set @SQLQuery = @SQLQuery + ' And (JoinDate
         BETWEEN '''+ convert(varchar, @StartDate, 103) +''' AND '''+convert(varchar, @EndDate, 103)+''')'
Exec (@SQLQuery)

conversion faild when converting date time from charachter string error

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0