Format Models

The default format is also important to implicit conversions. For example, if you insert a string value into a date column, a TO_DATE is executed implicitly. For more information, refer to the Type Conversion Rules section.

If no language is specified, then the current session language is used for representing the date. The current session language is defined in the NLS_DATE_LANGUAGE session parameter. It can be changed for the current session using ALTER SESSION or for the entire database using ALTER SYSTEM. The current values for the NLS parameters can be found in the EXA_PARAMETERS system table.

For the abbreviated month and day formats, and month and day formats written in full form, the representation of the first two letters can be varied by the use of uppercase and lowercase letters. The table below provides you with more details and examples.

The following table lists all the elements that you can in the date/time format model.

An element in the format model can only appear once. However, elements such as separators, padding characters, and the output format to TO_CHAR (datetime) can appear more than once.

Examples

-- Is interpreted as 1 February 2003
SELECT TO_DATE('01-02-2003','DD-MM-YYYY');

-- Is interpreted as 10 February 2003
SELECT TO_DATE('06-2003-MON','WW-YYYY-DY');

-- Is interpreted as 31 December 2003, 12:59:33
SELECT TO_TIMESTAMP('12:59:33 365-2003', 'HH24:MI:SS DDD-YYYY');

-- Is interpreted as 24 December 2009, 23:00:00
SELECT TO_TIMESTAMP('2009-12-24 11:00:00 PM','YYYY-MM-DD HH12:MI:SS AM');

-- Is interpreted as 12 May 2003, 00:00:10.123
SELECT TO_TIMESTAMP('2000_MAY_12 10.123','YYYY_MONTH_DD SS.FF3');

SELECT TO_CHAR(DATE '2007-12-31','DAY-Q-DDD; IYYY\IW') TO_CHAR1,
    TO_CHAR(DATE '2000-01-01','DAY-Day-day-')      TO_CHAR2;

When converting a string to a timestamp using TO_TIMESTAMP, only the number of fractional seconds digits specified by FF[1-9] are included. The remaining digits will be truncated.

-- This is the default value of NLS_TIMESTAMP_FORMAT:
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6';

-- The string is implicitly cast to TIMESTAMP using NLS_TIMESTAMP_FORMAT as
-- timestamp'1111-11-11 11:11:11.123456'. The condition then evaluates to false.
select '1111-11-11 11:11:11.123456789' = timestamp'1111-11-11 11:11:11.123456789';

Numeric format models

The following table lists all the elements that you can in the numeric format model.

Keep in mind these general considerations when working with numeric format models:

• If the format string is too short for the number, a string is returned, which is filled out with the character, #.
• The group and decimal separators are defined in the NLS_NUMERIC_CHARACTERS parameter and can be changed for the current session using ALTER SESSION or for the entire database using ALTER SYSTEM.
• A point is used for the decimal separator and a comma for the group separator by default. You can find the current values for the NLS parameters in the EXA_PARAMETERS system table.

The following examples illustrate the use of numeric format models in function TO_CHAR (number).