A format model is a string literal that represents a conversion rule between strings and numeric or date/time. When you convert a string to a number or date, the format model determines how Exasol interprets the string. By using format models, it is possible to assign a string to a numeric column or output a date as a string in the desired format.
Date/Time Format Models
Date/Time format models can be used in the functions TO_CHAR (datetime)/ TO_DATE/ TO_TIMESTAMP and in ETL commands IMPORT/ EXPORT. If no date/time format is specified, the current default format is used. This default format is defined in the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT session parameters. It can be changed for the current session using ALTER SESSION or for the entire database using ALTER SYSTEM.
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 upper case and lower case 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.
Note: 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.
|- : ; . /_ => / \ _||Serve as separators or padding characters. You can also use space|
|IYYY,vYYY and IYY,vYY,IY,vY,I,v||Year (0001-9999 per the international standard, ISO 8601) and the last 3,2, or 1 digits|
|YYYY and YYY,YY,Y||Year (0001-9999) and the last 3,2 or 1 digits|
|VYYY and VYY,VY,V||Year corresponding to the element VW, and the last 3,2 or 1 digits|
|Q||Quarter of the year (1-4)|
|MON||Abbreviation for month (JAN-DEC (ENG) or JAN-DEZ (DEU))|
|MONTH||Month written in full (JANUARY-DECEMBER (ENG) or JANUAR-DEZEMBER (DEU))|
|IW,vW||Week in the year (01-53, Mo-Su, week 01 of the year contains the 4th of January, previous days count to previous year, per the international standard, ISO 8601)|
|uW||Week in the year (00-53, Mo-Su, week 01 of the year contains the 4th of January, previous days count to week 00)|
|VW||Week in the year (01-53, Su-Sa, week 01 of the year starts with the first sunday, previous days count to to previous year)|
|UW||Week in the year (00-53, Su-Sa, week 01 of the year starts with the first sunday, previous days count to week 00)|
|WW||Week in the year (01-53, the first day of the year is the beginning of the week)|
|J||Julian Date (number of days since January 1, 4713 BC)|
|D||Day of week (1-7, starting from the day specified by the parameter NLS_FIRST_DAY_OF_WEEK. For more information, refer to ALTER SYSTEM)|
|ID||ISO day of week (1-7, starting from Monday)|
|DDD||Day in the year (001-365 or 001-366)|
|DAY||Weekday written in full (MONDAY-SUNDAY (ENG) or MONTAG-SONNTAG (DEU))|
|DY||Weekday abbreviation (MON-SUN (ENG) or MO-SO (DEU))|
|HH24 (or HH)||Hour (00-23)|
|PM,P.M.,pm,p.m||Similar to AM|
|FF[1-9]||Fraction of a second (for example, milliseconds at FF3)|
|T||ISO8601/JSON style date time separator|
|Z||ISO8601/JSON style UTC timezone designator|
-- 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;
Numeric Format Models
Numeric format models specify the interpretation or presentation of strings/numbers and can be used in functions TO_CHAR (number)/ TO_NUMBER and in ETL commands IMPORT/ EXPORT.
The following table lists all the elements that you can in the numeric format model.
|9||9999,999||Each 9 stands for a digit of the number. If the number has fewer digits, it is padded with leading spaces.|
|0||0000,000||Each 0 stands for a digit of the number. If the number has fewer digits, it is padded with 0(s).|
|S||S9999,999||Returns '+' for positive numbers and '-' for negative numbers. S may only be used at the beginning or end of the format string.|
|MI||9999,999MI||Returns a space character for positive numbers and '-' for negative numbers. MI can only be used at the end of the format string.|
|FM||FM9999,999||Removes all superfluous zeros. Returns '-' fir negative numbers and removes all leading and trailing spaces for positive numbers. FM can only be used at the beginning of the format string.|
|.||999.999||Returns a decimal point before the fractional part.|
|D||999D999||Returns the decimal separator from NLS_NUMERIC_CHARACTERS before the fractional part.|
|,||999,999.999||Returns a comma as a group separator at the specific position.|
|G||999G999D999||Returns the group separator from NLS_NUMERIC_CHARACTERS at the specified position.|
|X||XXXX||Each X stands for a hexadecimal digit of the number (chars A-F in upper case). If the number has fewer digits, it is padded with leading spaces. If the format string has a leading 0, then the string is padded with zeros instead.|
|x||xxxx||Each x stands for a hexadecimal digit of the number (chars a-f in lower case). If the number has fewer digits, it is padded with leading spaces. If the format string has a leading 0, then the string is padded with zeros instead.|
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).
|123||999||'123'||Standard format for positive integers|
|-123||999||'-123'||Standard format for negative integers|
|123.456||999.999||'123.456'||Standard format for positive decimals|
|-123.456||0999.9999||'-0123.4560'||Format filled with zeros|
|123.456||999.999MI||'123.456 '||Sign at the end|
|-123.456||999.999MI||'123.456-'||Sign at the end|
|123.456||FM999.999||'123.456'||Removes trailing spaces and zeros|
|123456.789||999,999.999||' 123,456.789'||Group separator|