You want to display the date for the first Mon day of the next month and issue the following command:
SQL>SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’MON’),
‘dd "is the first Monday for" fmmonth rrrr’)
FROM DUAL;
What is the outcome?
A.
It executes successfully and returns the correct result.
B.
It executes successfully but does not return the correct result.
C.
It generates an error because TO_CHAR should be replaced with TO_DATE.
D.
It generates an error because rrrr should be replaced by rr in the format string.
E.
It generates an error because fm and double quotation marks should not be used in the format string.
For choice A:
Shouldn’t it be ‘MONDAY’, rather than ‘MON’?
example:
next_day(’01-Aug-03′, ‘TUESDAY’) -> return ’05-Aug-03′
next_day(’06-Aug-03′, ‘SUNDAY’) -> return ’10-Aug-03′
why is A correct?
SYSDATE = 01/03/2013 (DD/MM/YYYY)
First, it takes the last Monday of March – 25/03/2013 (LAST_DAY(SYSDATE))
And then, the next Monday of the year in this case 01/04/2013
SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’LUNDI’),’dd “is the first Monday for” fmmonth rrrr’)
FROM DUAL;
Result
01 is the first Monday for avril 2013
just to correct it takes first 31/03/2013 (Sorry about that)
what is the role of ‘fm’ in fmmonth? why is it required?
Role of fm in fm month:
Fill mode. Oracle uses trailing blank characters and leading zeroes to fill format
elements to a constant width. The width is equal to the display width of the largest
element for the relevant format model
SQL> SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’TUE’),’dd “is the first Tuesday for” fmmonth rrrr’)from dual;
TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’TUE’),’DD”ISTHEFIRSTTUESDAYFOR”FM
———————————————————————
06 is the first Tuesday for october 2015
SQL> SELECT TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’TUE’),’dd “is the first Tuesday for” month rrrr’)from dual;
TO_CHAR(NEXT_DAY(LAST_DAY(SYSDATE),’TUE’),’DD”ISTHEFIRSTTUESDAYFOR”MO
———————————————————————
06 is the first Tuesday for october 2015