You need to display the date 11-Oct-2007 in words as Eleventh of October, Two Thousand
Seven. Which SQL statement would give the required result?
A.
SELECT TO_CHAR(’11-oct-2007′, ‘fmDdspth “of” Month, Year’) FROM DUAL;
B.
SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdspth of month, year’) FROM DUAL;
C.
SELECT TO_CHAR(TO_DATE(’11-oct-2007′), ‘fmDdthsp “of” Month, Year’) FROM DUAL;
D.
SELECT TO_DATE(TO_CHAR(’11-oct-2007′,’fmDdspth ”of” Month, Year’)) FROM DUAL;
Explanation:
Using the TO_CHAR Function with Dates
TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format
specified by the format_model. A format model is a character literal that describes the format of
datetime stored in a character string. For example, the datetime format model for the string ’11-Nov-1999′ is ‘DD-Mon-YYYY’. You can use the
TO_CHAR function to convert a date from its default format to the one that you specify.
Guidelines
The format model must be enclosed with single quotation marks and is case-sensitive.
The format model can include any valid date format element. But be sure to separate the date
value from the format model with a comma.
The names of days and months in the output are automatically padded with blanks.
To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
Elements of the Date Format Model
———————————————————————DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
MM Two-digit value for the month
MON Three-letter abbreviation of the month
MONTH Full name of the month
YYYY Full year in numbers
YEAR Year spelled out (in English)
Correct Answer is A.
Option c is Incorrect because of the format(fmDdthsp). It should be fmDdspth.
option C is Correct. Apologies