Re: Boundary of date in Oracle?

Hi There,

You can have the solution, use RRRR instead of YYYY, this is used for changing milenia.

If the current year is in the first half of the century(years 0 to 49) then:
- If you enter a date in the first half of the century, RR returns the
current century.
- On the other hand, if you enter a date in the later half of the century,
RR returns the previous century.

If the current year in the later half of the century (years 50 to 99) then:
- If you enter a date in the first half of the century, RR returns the next
century.
- If you enter the date in the later half of the century, RR returns the
current century.

Try it out :
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY')

,                TO_CHAR(TO_DATE('14-OCT-80', 'DD-MON-RR'), 'YYYY')
,                TO_CHAR(TO_DATE('14-OCT-18', 'DD-MON-RR'), 'YYYY')
FROM       DUAL; 

And the results would be:
11/21/1997 ---> Current date

1980          ----> Year 88 with century as 19
2018         ----> Year 18 with century as 20

PS: If you use the RR format after the year 2000 and want to enter a date that falls in later half of the 21st century, you need to add special logic.

Hope this might help you.

Cheers
Pravesh Godiyal

-- 
come out with more new quetions to make the forum intresting