TO_CHAR

The TO_CHAR function can be used to convert a datetime value to a variable-length string. This single function works for DATE types as well as for all the types in the TIMESTAMP family. TO_CHAR is also used to convert numbers to character strings, as covered in Chapter 9. The following specification describes TO_CHAR for datetime values:

FUNCTION TO_CHAR ( date_in IN DATE [, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2]])RETURN VARCHAR2

where:

 

date_in

Is the date to be converted to character format.

 

format_mask

Is the mask made up of one or more of the date format elements. See Table 10-1 for a list of date format elements.

 

nls_language

Is a string specifying a date language.

Both the format_mask and nls_language parameters are optional.

If you wish your results to be in the national character set, you can use TO_NCHAR in place of TO_CHAR. In this case, be certain you provide your date format string in the national character set as well. Otherwise, you may receive ORA-01821: date format not recognized errors.

 

If format_mask is not specified, the default date format for the database instance is used. This format is DD-MON-RR, unless your DBA has used the NLS_DATE_FORMAT initialization parameter to specify some other format. You can also change the default format for a given session using the ALTER SESSION command:

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

If the NLS language parameter is not specified, then the default date language for the instance is used. This is either the language for the instance specified by the NLS_LANGUAGE parameter, or the date language specified by the initialization parameter NLS_DATE_LANGUAGE.

Here are some examples of TO_CHAR's being used for date conversion:

· Notice that there are two blanks between month and day and a leading zero for the fifth day:

TO_CHAR (SYSDATE, 'Month DD, YYYY') --> 'February 05, 1994'

· Use the FM fill mode element to suppress blanks and zeros:

TO_CHAR (SYSDATE, 'FMMonth DD, YYYY') --> 'February 5, 1994'

· Note the case difference on the month abbreviations of the next two examples. You get exactly what you ask for with Oracle date formats!

· TO_CHAR (SYSDATE, 'MON DDth, YYYY') --> 'FEB 05TH, 1994'TO_CHAR (SYSDATE, 'fmMon DDth, YYYY') --> 'Feb 5TH, 1994'

The TH format is an exception to the capitalization rules. Even if you specify lowercase "th" in a format string, Oracle will use uppercase TH in the output.

· Show the day of year, the month, and the week for the date:

· TO_CHAR (SYSDATE, 'DDD DD D ') --> '036 05 7'TO_CHAR (SYSDATE, 'fmDDD DD D ') --> '36 5 7'

· Here's some fancy formatting for reporting purposes:

· TO_CHAR (SYSDATE, '"In month "RM" of year "YEAR') --> 'In month II of year NINETEEN NINETY FOUR'

· For TIMESTAMP variables, you can specify the time down to the millisecond:

· TO_CHAR (A_TIMESTAMP, 'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM') --> a value like: 2002-02-19 01:52:00.123457000 PM -05:00

Be careful when dealing with fractional seconds. The FF format element represents fractional seconds in the output format model, and you'll be tempted to use the number of Fs to control the number of decimal digits in the output. Don't do that! It doesn't work. The following example attempts to use FFFFF to specify five decimal digits:

DECLARE A TIMESTAMP WITH TIME ZONE;BEGIN A:= TIMESTAMP '2002-02-19 13:52:00.123456789 -5:00'; DBMS_OUTPUT.PUT_LINE(TO_CHAR(a,'YYYY-MM-DD HH:MI:SS.FFFFF AM TZH:TZM'));END;

The output is:

DECLARE*ERROR at line 1:ORA-01821: date format not recognizedORA-06512: at line 5

If you want only five decimal digits, then you must declare your timestamp variable as TIMESTAMP(5). For example:

DECLARE A TIMESTAMP(5) WITH TIME ZONE;BEGIN A:= TIMESTAMP '2002-02-19 13:52:00.123456789 -5:00'; DBMS_OUTPUT.PUT_LINE(TO_CHAR(a,'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM'));END;

The output is:

2002-02-19 01:52:00.123460000 PM -05:00 PL/SQL procedure successfully completed.

Note the rounding that occurred. The number of seconds input was 00.123456789. That value was rounded (not truncated) to five decimal digits: 00.12346.

It's unfortunate that Oracle ties the number of decimal digits displayed directly to the precision of the timestamp variable in question. We believe you should be able to control the display precision independently of the stored precision. NUMBERs work that way; TIMESTAMPs should too.

 

It's easy to slip up and specify an incorrect date format, and the introduction of TIMESTAMP types has made this even easier. Format elements that are valid with TIMESTAMP types are not valid for the DATE type. Note the results in the following example when FF, TZH, and TZM are used to convert a DATE value to a character string:

DECLARE A DATE;BEGIN A:= SYSDATE; DBMS_OUTPUT.PUT_LINE(TO_CHAR(A,'YYYY-MM-DD HH:MI:SS.FF AM TZH:TZM'));END;

The output is:

A:= SYSDATE;*ERROR at line 4:ORA-01821: date format not recognizedORA-06512: at line 5

The error message you get in this case, ORA-01821: date format not recognized, is confusing and misleading. The date format is just fine. The problem is that it's being applied to the wrong datatype. Watch for this when you write code. If you get this error, check not only the date format, but also the datatype that you are trying to convert.