Help!EXEC () does not like dates 2

[kaiuweb]

Hi,
I am trying to dynamically build sql statement for a search form in a stored procedure.
All seems to be going well until dates are entered.
I have tried everything I can think of but keep getting error messages about converting strings to dates.

When I run the select statement below;
storedproc
@myDate datetime
as
select * from table where date > @myDate
return

and enter the date 10/02/03 (uk style)then all is ok.

But when I do the following
storedproc
@myDate datetime

as
declare @myStr varchar(500)
set @myStr = 'select * from thetable where thedate > ' + @myDate
exec (@myStr)

...things go wrong.
I have tried convert and cast, but obviously I am doing something wrong.
ps The reason for doing it this way is because there are other conditions that have to be met, the date is just one part and the code above is a snippet of the actual thing which does works - except for the dates.
Any ideas,anyone?

 

[osjohnm]

Give this a try:
--create etc
@myDate varchar(50)
as
declare @myStr varchar(500)
set @myStr = 'select * from thetable where thedate > ' +''''+@myDate+''''
exec (@myStr)

Hope this helps

John

 

[kaiuweb]

Hi John,
Thanks for the reply.
I have tried your code and the error converting datetime to string has gone, unfortunately it isn't returning any records either.
Thanks anyway.

Kai

 

[SQLSister]

Since you didn't give us the complete code, it is hard for us to see what further problems you might have. However I suggest you add a debug statement of Select @myStr right after you set the value to see what sql is reall being sent tin the execute statement. You probably have built the SQL statement so that it creates the statement incorrectly and it doen't do what you really wanted.

 

[TLGsoft]

do a PRINT @mystr to see what are you trying to execute

 

[osjohnm]

kaiuweb, as SQLSister mentioned you didn't post your full code, only a snippet. I tested the sql I posted by first replacing the exec(@mystr) with select(@mystr)
and @mystr contained the correct select statment. I then psoted the above sql. That part works but I'm guessing that there is something further on that might not be working properly.

Use the select(@mystr) as SQLsister suggested to test what your string contains. You will notice that I added a lot of inverted commas..'''' before and after @mydate. The reason being that when you uses dates in a select the date part is always inclosed with quotes eg: '2003-02-26'
If you use the select(@mystr) check to see if you can see the quotes around the date.

Hope this helps

John

 

[kaiuweb]

Hi All and thanks for the replies.
I could put the whole code down but I can't even get this bit to work:

Alter Procedure procGetAllEnquiriesLike
@myENQDATE varchar(50)
As
declare @mySQLSTR1 varchar(500)
set @mySQLSTR1 = 'SELECT TBL_ENQUIRY.* FROM TBL_ENQUIRY WHERE (TBL_ENQUIRY.ENQ_DATE_REC > ' +''''+ @myENQDATE +''')'
EXEC (@mySQLSTR1)
--select @mySQLSTR1
return


When I run the select @mySQLSTR1 as mentioned earlier by SQLsister I get this:
SELECT TBL_ENQUIRY.* FROM TBL_ENQUIRY WHERE (TBL_ENQUIRY.ENQ_DATE_REC > '10/02/03')
...which looks ok to me.

I have checked the table itself.
The field ENQ_DATE_REC is stored as standard datetime
and there are at least 20 records that should be returned
with this procedure.But all I am getting is an empty recordset allowing me to add a new record.

I am using SQL Server 7 and connecting to it with an Access adp if that makes any difference.

Thanks for all your replies anyway.

 

[JamesLean]

You could have a problem with an ambiguous date format. Try executing your SP and input '20030210' rather than '10/02/03'. This is ISO format and therefore won't be misinterpreted by SQL Server. --James

 

[kaiuweb]

Well done JamesLean.
Your suggestion works and those records are now coming back to me.
Thanks.

Any ideas what to do now.
The end user of the search form is expecting to type in 10/02/03.
Is there something that needs 'tweaking' or should I put in extra code that changes the date format to '20030210'.

Much appreciated James.
And thanks to all who replied.
Kai

 

[JamesLean]

Whenever I have users inputting dates, I have seperate inputs for each part of the date (ie day, month, year) and then build the date in the ISO format when sending the SQL query.

Exactly how you do this depends on your front-end app - just remember to include leading zeroes if necessary.

NB: you can either use yyyymmdd or yymmdd. --James

 

[kaiuweb]

Thanks again James and all of you who replied [Thumbsup2]