Oracle between dates query question

From: Mike Fellows <mike.fellows_at_btinternet.com>
Date: Mon, 6 Dec 1999 18:39:27 -0000
Message-ID: <82h00o$c17$1@plutonium.btinternet.com>

Hi There !!!
I have inherited a system which we were led to be believed was Y2K = compliant however we have discovered a problem. I am working on a script at the moment (Oracle Ver 7.2.3, Forms 3) that = is giving he a couple of problems ....
I have a form that users access to enter their hour worked details into. One of the control fields on this form holds a week ending parameter = (DD-MON-YY)
Once this field has been entered by the user the form then accesses a = table called MONTH to find out which month the week endind date belongs = to.This table has 3 fields, those being

1. Paymonth (Char)
2. Start_Date (DD-MON-RR)
3. End_Date (DD_MON_RR)

A typical example of data held within the MONTH table would be

Paymonth Start_Date End_Date

Jan-1999 15-Dec-98 10-Jan-99
Feb-1999 11-jan-99 13-Feb-99
Mar-1999 14-Feb-99 15-Mar-99

.
.
.

Dec-1999 15-Nov-99 06-Dec-99
Jan-2000 07-Dec-99 10-Jan-00

(Incidentally the NLS_DATE_FORMAT on our server is DD-MON-RR also.)

The Idea being the user enters a week ending of say 05-DEC-99 and the = system recognises this as belonging to the Dec-1999 paymonth. As you may have guessed, all is OK until we try to enter a week ending = date that falls into the year 2000 then we get a no data found error.

The procedure that works out the paymonth looks like this..

SELECT PAYMONTH FROM MONTH M
WHERE :CONTROL.SCREEN_WEEK_ENDING_DATE BETWEEN TO_DATE(TO_CHAR(M.START_DATE,'DD-MON-RR')) AND TO_DATE(TO_CHAR(M.END_DATE,'DD-MON-RR')); I have tried adapting the procedure without any success.

As the system stands it will only calculate the correct paymonth for = week endings before 2000
so any help / advice would be greatly appreciated

Thanks in advance

Mike Received on Mon Dec 06 1999 - 12:39:27 CST