SQL TO_CHAR Function
SQL TO_CHAR function converts a date value to a character format. The input string should be of DATE data type. SQL TO_CHAR function consists of two parameters, input date string and format string. The date string parameter represents the date that is to be converted, while the format string represents the format that the DATE value will be converted into. SQL TO_CHAR function is mostly used to fetch specific value from date string (such as date, month, and year) or to make it more readable.
Example 1:
The following SQL TO_CHAR function query will display current year. This will return ‘2013’ as result.
SELECT TO_CHAR (SYSDATE,’YYYY’) from dual;
Output:
Note: DUAL is a dummy table used by Oracle. The format picture ('YYYY') tells Oracle the format of output data string. The function retrieves year value from SYSDATE which represents current date.
Example 2:
The following SQL TO_CHAR function example converts standard date into more readable format. This returns ’18 DEC 2013’ as a result
SELECT TO_CHAR(SYSDATE,’DD MON YYYY’) from dual;
Output:
The date format picture is composed of date format elements which are discussed in detail in following topic: