Home » Developer & Programmer » Forms » date problem
| date problem [message #87781] |
Thu, 10 February 2005 16:08  |
hudo
Messages: 165
Registered: May 2004
|
Senior Member |
|
|
Hello,
I got 4 text items in a form , STARTDATE, STOPDATE (of Data Type: Date),
and STARTTIME, STOPTIME (of Data Type: Datetime) in a form which serve as
input parameters for a stored procedure (all 4 of type date in procedure header)
Then I would like to concatenate STARTDATE and STARTTIME to datestart
and STOPDATE and STOPTIME to datestop with:
datestart VARCHAR2(20);
datestart := TO_CHAR(STARTDATE, 'dd.mm.yyyy')||' '||TO_CHAR(STARTTIME, 'hh24:mi');
The procedure includes
SELECT ONE, TWO, THREE FROM MYTABLE
WHERE TIMECOL >= TO_DATE(datestart,'dd.mm.yyyy HH24:MI')
AND TIMECOL <= TO_DATE(datestop,'dd.mm.yyyy HH24:MI')
But this seems not to work.
Please help
|
|
|
|
| Re: date problem [message #87794 is a reply to message #87781] |
Sat, 12 February 2005 02:58  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hudo,
The way you are comparing dates is wrong.
Let's take an example.
You need to fetch data between Dates:
10-Jan-2005 12:00:00 and 01-Oct-2005 12:00:00
E.g.#1
Here we will use your format i.e. dd/mm/yyyy hh24:mi:ss
SQL> select 1 from dual where
to_date('10/01/2004 12:00:00','dd/mm/yyyy hh24:mi:ss')
> to_date('01/10/2004 12:00:00','dd/mm/yyyy hh24:mi:ss')
2 /
no rows selected
Example #2
Now lets chnage the date format to mm/dd/yyyy hh24:mi:ss
SQL> select 1 from dual where
to_date('10/01/2004 12:00:00','mm/dd/yyyy hh24:mi:ss') > to_date('01/10/2004 12:00:00','mm/dd/yyyy hh24:mi:ss')
2 /
1
---------
1
So, try to convert the format and then let us know if the problem persists.
Also it will be better if you first store the dates in some variable as follows:
L_start_dt:=to_date(datestart,'mm/dd/yyyy hh24:mi:ss');
L_end_dt:=to_date(datestop,'mm/dd/yyyy hh24:mi:ss');
And chnage your where clause as
WHERE TIMECOL >= L_start_dt
AND TIMECOL <= L_end_dt
This will result in good coding practice for you as your query will not be parsed again & again each time you use this Procedure due to usage of bind variables.
HTH
Regards
Himanshu
|
|
|
|
| Re: date problem [message #87800 is a reply to message #87794] |
Sat, 12 February 2005 13:48 |
hudo
Messages: 165
Registered: May 2004
|
Senior Member |
|
|
Himanshu,
I think you misunderstood me. In the form I got 4 text items, 2 items where the date part is determind (these two are STARTDATE and STOPDATE), they are of data type "DATE" in the form, and 2 items where the "hour,minute" part is determined (STARTTIME, STOPTIME), they are of data type "DATETIME" in the form. The data block where the result is displayed, is based on a stored procedure
with values of the 4 form text items as input parameters:
(BTW: In forms exists exist the data types DATE,DATETIME , but in SQL exists only the data type DATE. Is this correct ?)
MYPROC(RESULTSET IN OUT MY_CURSOR, STARTDATE DATE, STARTTIME DATE, STOPDATE DATE, STOPTIME DATE)
IS
l_start_dt VARCHAR2(16);
l_end_dt VARCHAR2(16);
BEGIN
-- here concatenate the date part and the "hour/minute" part to the complete date, maybe this is not correct ?
l_start_dt := TO_CHAR(STARTDATE, 'dd.mm.yyyy')||' 'TO_CHAR(STARTTIME, 'hh24:mi');
l_end_dt := TO_CHAR(STOPDATE, 'dd.mm.yyyy')||' 'TO_CHAR(STOPTIME, 'hh24:mi');
-- formulate the select
OPEN RESULTSET FOR
SELECT MYTAB.COL_ONE, MYTAB.COL_TWO, MYTAB.TIMECOL
WHERE
MYTAB.TIMECOL >= TO_DATE(l_start_dt,'dd.mm.yyyy hh24:mi')
AND MYTAB.TIMECOL < TO_DATE(l_end_dt,'dd.mm.yyyy hh24:mi')
;
END;
So, the customer likes to determine the complete date as a separate dd.mm.yyyy part and a separate hh24:mi part, thats the reason for the 4 text items in the form. In the procedure I'd like the rebuild the complete date for these two separate parts. I don't think this is possible,if l_start_dt is of data type DATE, thats why l_start_dt is of VARCHAR2 type.
Then in the select statement this concatenated date parts are used to isolate the time period...
I make use of bind variables, l_start_dt and l_end_dt, as you mentioned.
To your eg #1: this is obviously, that a day from january 2004 is not "greater" than a day from october 2004.
I did not catch the point what you'd liked to demonstrate with your two examples.
|
|
|
|
Goto Forum:
Current Time: Sun Nov 03 09:47:17 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
