Passing Date Paramaters... Urgent!

Hi guys,

How do you pass a date (with time) paramater to a procedure??

I have a packages with a procedure that takes in two input paramater that are defined as DATE and third paramater as a refcursor. I execute the following:

SQL> var a refcursor
SQL> exec packages_pkg.p_proc('15-JUN-01', '15-JUN-01', :a)


The above works... however, I need to pass the time as well. I tried every combination... nothing seems to work.

Any help would greatly be appreciated.

Thanks.
- Gary

exec packages_pkg.p_proc(to_date('15-JUN-01:03:31','DD-MON-YY:HH:MI'), '15-JUN-01', :a)

This is absolutely mind-boggling. When I execute the following statement in SQL*PLUS, I get different results than what the front-end application gives me. The front end gives me correct results. Testing in SQL*PLUS gives me incorrect results. Is this the proper way to send dates(with time) to a procedure?

SQL> var a refcursor
SQL> exec package_pkg.p_proc(to_date('15-JUN-01:03:00','DD-MON-YY:HH:MI'), to_date('15-JUN-01:03:30','DD-MON-YY:HH:MI'), :a)

Within the procedure I check both of these dates against dates in a specific table that are defined as DATE.

Any help would great be appreciated.

Thanks,
- Gary

Can you post the PL/SQL code please.

- Chris

The code is rather complicated. Essentially, what it does is check the availability of crew members based on a specific start date and end date. For example, if crew member John Doe is shooting a video on June 26th at 3:00 to 3:30, the procedure should flag him unavaliable if I try to book him for a different event between 3:00 to 4:00. Any overlaping of time should make him unavailable. The procedure is broken up into two SELECT statements, one for available crew members, and one for unavailable crew members for the specific dates being passed to the procedure.

-------------------------------------------------

procedure p_AvailableCrew_Get
(v_StartDateTime IN Video.StartDateTime%TYPE,
v_EndDateTime IN Video.EndDateTime%TYPE,
out_Inventory out cur_Inventory)

is
cv_Inventory cur_Inventory;
err_num number;
err_msg varchar2(200);
UnavailableFlag number := 0;
AvailableFlag number := 1;

begin
OPEN cv_Inventory FOR
SELECT DISTINCT tu.UserID,
tu.UserFirstName,
tu.UserLastName,
AvailableFlag
FROM CSFBTVAdmin.CSFBTVUser tu,
CSFBTVAdmin.UserRole ur
WHERE tu.UserID NOT IN(SELECT UserID FROM CSFBTVAdmin.VideoCrew tc,
CSFBTVAdmin.Video tv
WHERE tc.VideoID = tv.VideoID
AND ((StartDateTime > v_StartDateTime AND StartDateTime < v_EndDateTime)
OR (EndDateTime > v_StartDateTime AND EndDateTime < v_EndDateTime)
OR (StartDateTime <= v_StartDateTime AND EndDateTime >= v_EndDateTime)))
AND tu.UserID = ur.UserID
AND ((ur.RoleID = 3) OR (ur.RoleID = 4))
AND ActiveFlag = 'Y'
UNION
SELECT DISTINCT tu.UserID,
tu.UserFirstName,
tu.UserLastName,
UnavailableFlag
FROM CSFBTVAdmin.CSFBTVUser tu,
CSFBTVAdmin.UserRole ur,
CSFBTVAdmin.VideoCrew tc,
CSFBTVAdmin.Video tv
WHERE tu.UserID = ur.UserID
AND ((ur.RoleID = 3) OR (ur.RoleID = 4))
and tu.UserID = tc.UserID
AND tc.VideoID = tv.VideoID
AND ((StartDateTime > v_StartDateTime AND StartDateTime < v_EndDateTime)
OR (EndDateTime > v_StartDateTime AND EndDateTime < v_EndDateTime)
OR (StartDateTime <= v_StartDateTime AND EndDateTime >= v_EndDateTime))
AND ActiveFlag = 'Y';

out_Inventory := cv_Inventory;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM,1,200);
RAISE_APPLICATION_ERROR(-20005,'Get procedure fail '||err_msg);
end p_AvailableCrew_Get;

------------------------------------------------

Thanks,
- Gary