Re: Boundary of date in Oracle?
Date: 1997/11/21
Message-ID: <01bcf689$34dd44c0$a63566a1_at_eusunw2415.rec.bp.com>#1/1
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 intrestingReceived on Fri Nov 21 1997 - 00:00:00 CET