Stored Procedure Dynamic Date madness...

[Shilohcity]

Hi there

I have a stored query that is using dynamic dates which are entered via an asp page. In the stored procedure below I have managed to get the first instance of the variable in lines 9 - 11 where I use convert(datetime,@start,101) to work for me however when I try to call the same variable again in the set @SQL lines of code however I get an error that says "Syntax error converting datetime from character string." This is really starting to mess with my head as if I enter hard coded dates as below it works fine but as soon as I enter (or try to) the variables I get errors. I get errors related to declaring variables, syntaxt converting char -> date and invalid date ranges.


CREATE PROCEDURE [gz_mr_winner]
(@start datetime,
@finish datetime)
AS
set dateformat MDY
declare @counter int
declare @SQL varchar(500)

set @counter = (select count(*) from flt_pnr_detail where fdm_booking_date between convert(datetime,@start,101) and convert(datetime,@finish,101) and booking_agent = 'INET')

set @counter = (select round(rand()*@counter,0))

set @SQL = 'select top 1 fdm_rec_loc from flt_pnr_detail where fdm_rec_loc in (select top ' + rtrim(ltrim(str(@counter))) + "fdm_rec_loc from flt_pnr_detail where fdm_booking_date between '2001/05/31' and '2001/05/31' and booking_agent = 'INET' order by fdm_rec_loc) order by fdm_rec_loc desc"

execute (@SQL)

Does anybody have any help, hope or humor to cheer me up?

Thanks
Justin. X-) "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[tlbroadbent]

Try this one and let me know if it works.

Set @SQL =
"Select Top 1 fdm_rec_loc
From flt_pnr_detail
Where fdm_rec_loc In
(Select Top " + ltrim(str(@counter)) + " fdm_rec_loc
From flt_pnr_detail
Where fdm_booking_date
Between '" + convert(datetime,@start,101) +
"' And '" + convert(datetime,@finish,101) +
"' And booking_agent = 'INET'
Order By fdm_rec_loc)
Order By fdm_rec_loc desc" Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[Shilohcity]

Hi Terry

Thanks for your suggestion but alas still an error related to character string conversion. I am getting a little fed up with this as Im almost 100% sure that I have all my date formats sorted and it seems to not make any sense at the moment.

Well I am going to put this on the back burner of my brain for a little while but will get back to you when (not if :->)I solve this.

Thanks
Justin. :-x "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz