SQL query to retrieve records between 2 dates. 1

[ml23sh]

I'm using an Oracle database, and I have written a query that takes a users input and retrieves that info from the database. The whole thing works fine except when the user wants to search for records between 2 dates.

This call works fine:
Player.player_id LIKE'%"+ rsGS__v_PlayerID.replace(/'/g, "''&quot +"%'

But this doesn't:
Games.game_date > (&quot;+ rsGS__v_GameID.replace(/'/g, &quot;''&quot+&quot;, 'DD-MON-YYYY')AND Games.game_date <
(&quot;+ rsGS__v_GameID.replace(/'/g, &quot;''&quot+&quot;, 'DD-MM-YYYY')

I figure the call to the database has to be goofed up somehow, because if I just harcode the values in, it works fine.

 

[vtj]

Is this related to the thread you posted on 7/13?

I'm not familiar with Oracle but ANSI SQL has a &quot;between&quot; predicate that should work for you.

Select Fld1, Fld2
From Tbl1
Where somedate between date1 and date2

somedate corresponds to the date in the file and date1, date2 correspond to the user's input.

This also works in imbedded SQL using host variables.

eg :mydate where mydate corresponds to some formula or hard coded date. (Where :mydate between date1 and date2) VTJ

 

[marathon42]

This tip was helpful in improving the coding-overview. I do however have a related problem. Perhaps you can help me on this:
I have to sort out hospital inpatients who have been in more than once in a given period, listing those periods.
Here is the syntax that works, BUT how can I solve the problem of having to input the dates twice??

select .....
from PATIENT A, OBJEKTE B
Where a.clinik_id = b.obj_id
and a.pid in (SELECT c.pid
FROM Patient c
WHERE c.pid IN
(SELECT pid
FROM patient
where admis_date
between :=date1 and :=date2
and exit_date is not null
GROUP pid HAVING COUNT(pid) > 1)
group by c.pid having count(*) > 1)

and admis_date between :=date1 and :=date2
and exit_date is not null
group by a.p_nr, b.obj_name, a.p_name, a.p_vorname,
a.admis_date, a.exit_date, a.ENTL_DIAGNOSE, a.pid
order by a.pid;