Bug: Date incorrectly showing in the display result

[ashish_mistry]

Try having a table with date as

01-Jan-2100 ..

eg i had a table ps_job with effdt as thedate field
SELECT ps_job.effdt,
to_char(ps_job.effdt,'DD-MON-YYYY'),
count(*)
FROM ps_job
WHERE ps_job.effdt >= to_date('01-JAN-2009', 'DD-MON-RRRR')
group by ps_job.effdt

the PLSQL developer result cell will have 01-jan-00
and the drop down date Gui date popup indicate
JAN 2000
1

the date format in the Preferences is Oracle format,
changing it to userdefined will not help

So when I was querying dates > Jan 2009 .. it gives me an idea that something is wrong in my query and 01-Jan-2000 results are returned

Thanks,
Ashish

 

[Marco Kalter]

It seems to me that the date format is incorrect, since 00 is never a correct representation of 2009. What happens if you switch to "Windows format" for the Date/Time preferences?

 

[Worker]

What is the output of

select to_char(sysdate, 'DD-MON-YYYY') from dual

 

[ashish_mistry]

Sry for the late reply was away for a while and then has some forum logon issues

Also to add the output of my query above that gives the date values in the format desired - i.e. to_char(date, 'DD-MON-YYYY')

---------------------------------------------------------------------
EFFDT TO_CHAR(PS_JOB.EFFDT,'DD-MON-Y COUNT(*)
02-Jan-99 02-JAN-2099 1
---------------------------------------------------------------------

Changing the format to Windows format - i.e. MM/dd/yyyy displays the date with year as 2100 as the entire 4 digits of the year are in the format and hence even the GUI calender opens up to the date 01-Jan-2100

So what i am trying to say is that the GUI calender popup should be based on the data in the table irresspective of the format of the date on the screen/result window

For the above result if I were to open the calender for the first column it would go to 02-Jan-1999 when the actual data is 02-Jan-2099 (displayed in 2nd column) and can be a little misleading

not sure if it would be a tough one to fix and can affect the performance and we can live with it or set the date format such that it is always in the windows or entire year translation

if the plsql developer always formats the data as it arrives it can be easy then

 

[Worker]

I think I finally understand this. It appears that if you set the date/time preference to "Oracle format" (DD-MMM-YY) and perform the following query:

select to_date('01-Jan-2100','dd-Mon-yyyy') from dual

then the pop-up calendar for the result cell will show the year 2000 rather than 2100.

That does seem like a bug, but it also seems hard to fix...

 

[Marco Kalter]

Using a YY date format is indeed not advisable.