Re: Oracle between dates query question

From: Alan Shein <alanshein_at_erols.com>
Date: Mon, 6 Dec 1999 13:49:13 -0500
Message-ID: <82h0f0$rv$1@autumn.news.rcn.net>

Sounds like the 2000 data was entered when the system was set for = dd-mon-yy. Query the 2000 data using

to_char(end_date , 'dd/mm/yyyy')

and see what you get. If the year is 1900, then you'll need to update = the data.
  Mike Fellows <mike.fellows_at_btinternet.com> wrote in message = news:82h00o$c17$1_at_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

Paymonth (Char)
Start_Date (DD-MON-RR)
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:49:13 CST