With the function TO_CHAR, Oracle converts a number or date to a string.
The to_char Oracle syntax is:
to_char( value, [ format_mask ], [ nls_language ] )
- value can either be a number or date that will be converted to a string.
- format_mask is optional. This is the format that will be used to convert value to a string.
- nls_language is optional. This is the nls language used to convert value to a string.
So, to convert a date variable to a text (string), you would use the to_char Oracle function.
TO_CHAR Oracle within SQL
select to_char(sysdate, ‘yyyy/mm/dd’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘2011/07/17’ */
select to_char(sysdate, ‘Month DD, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘October 17, 2011’ */
select to_char(sysdate, ‘FMMonth DD, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘October 17, 2011’ */
select to_char(sysdate, ‘FMMON DDth, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘OCT 17TH, 2011’ */
select to_char(sysdate, ‘FMMon ddth, YYYY’) as to_char_oracle from dual; /* to_char Oracle SQL returns: ‘Oct 17th, 2011’ */
TO_CHAR Oracle within PL/SQL
(Please turn serveroutput on)
SET serveroutput ON
Here, within PL/SQL loop with the function to_char oracle returns the number i converted to a text string:
BEGIN
FOR i IN REVERSE 1 .. 3 LOOP
DBMS_OUTPUT.put_line(TO_CHAR(i) ||’ – converted by to_char Oracle function’);
END LOOP;
END;
Result:
3 – converted by to_char Oracle function
2 – converted by to_char Oracle function
1 – converted by to_char Oracle function