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)