Format Models

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 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.

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.

Elements Meaning
- : ; . /_ => / \ _ Serve as separators or padding characters. You can also use space
CC Century (01-99)
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)
MM Month (01-12)
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)
DD Day (01-31)
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))
HH12 Hour (01-12)
HH24 (or HH) Hour (00-23)
AM,A.M.,am,a.m. Meridian indicator
PM,P.M.,pm,p.m Similar to AM
MI Minute (00-59)
SS Second (00-59)
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
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;

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.

Elements Examples Description
Digit
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).
Sign
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.
Decimal Separator
. 999.999 Returns a decimal point before the fractional part.
D 999D999 Returns the decimal separator from NLS_NUMERIC_CHARACTERS before the fractional part.
Group Separator
, 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.
Hexadecimal
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.
Others
eeee 9.99eeee Scientific notation.
EEEE 9.99EEEE Scientific notation.

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).

Number Format Result Description
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 S999.999 '+123.456' Explicit sign
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
123 XXX '7B' Hexadecimal strings
123 xxx '7b' Hexadecimal strings
123.4567 9.99eeee '1.23e2' Scientific notation
123.4567 9.99EEEE '1.23E2' Scientific notation