Format Models

This article explains how to use format models in Exasol.

A format model is a string literal that represents a conversion rule between strings and numeric or date/time data types. 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) and TO_DATE/ TO_TIMESTAMP, and in the ETL commands IMPORT and EXPORT. If no date/time format is specified, the current default format is used. The default format is defined in the NLS_DATE_FORMAT and NLS_TIMESTAMP_FORMAT session parameters, and can be changed for the current session using ALTER SESSION or system-wide using ALTER SYSTEM.

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

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

The following table lists the elements that can be used in the date/time format model.

ISO in the following table refers to date and time formats according to the ISO 8601 standard. For more information, see ISO 8601 (Wikipedia).

Element Description
- : ; . /_ => / \ _ Serve as separators or padding characters. You can also use a space character.
AM, A.M., am, a.m. Meridian indicator (morning)
CC Century (01–99)
D

Day of week (1–7, starting from the day specified by NLS_FIRST_DAY_OF_WEEK)

DAY Weekday written in full (ENG: MONDAY–SUNDAY, DEU: MONTAG–SONNTAG)
DD Day (01–31)
DDD Day in the year (001–365 or 001–366)
DY Weekday abbreviation (ENG: MON–SUN, DEU: MO–SO)
FF[1-9] Fraction of a second (for example, FF3 = milliseconds)
HH12 Hour (01–12)
HH24 or HH Hour (00–23)
ID ISO day of week (1–7, starting from Monday)
IW, vW ISO week number (01–53, 01 is the week with the first Thursday of January in it)

IYYY,vYYY

 ISO year (0001–9999)

IYY, vYY, IY, vY, I, v

 Last 3, 2, or 1 digits of ISO year. The current year is used to determine the preceding digit(s).
J Julian date (number of days since January 1, 4713 BCE)
MI Minute (00–59)
MM Month (01–12)
MON Abbreviation for month (ENG: JAN–DEC, DEU: JAN–DEZ)
MONTH Month written in full (ENG: JANUARY–DECEMBER, DEU: JANUAR–DEZEMBER)
PM, P.M., pm, p.m. Meridian indicator (evening)
Q Quarter of the year (1–4)
SS Second (00–59)
T ISO/JSON style date time separator
uW Week in the year (00–53, Mo–Su, week 01 contains the 4th of January, previous days count to week 00)
UW Week in the year (00–53, Su–Sa, week 01 starts with the first Sunday, previous days count to week 00)
VW Week in the year (01–53, Su–Sa, week 01 starts with the first Sunday, previous days count to to previous year)
VYYY Year corresponding to the element VW
VYY, VY, V Last 3, 2, or 1 digits of year corresponding to VW. The current year is used to determine the preceding digit(s).
WW Week in the year (01–53, the first day of the year is the beginning of the week)
YYYY Year (0001–9999)
YYY, YY, Y Last 3, 2, or 1 digits of the year. The current year is used to determine the preceding digit(s).
Z ISO/JSON style UTC timezone designator

Usage notes

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 
SELECT TO_DATE('01-02-2003','DD-MM-YYYY');
-- interpreted as 1 February 2003
SELECT TO_DATE('06-2003-MON','WW-YYYY-DY');
-- interpreted as 10 February 2003
SELECT TO_TIMESTAMP('12:59:33 365-2003', 'HH24:MI:SS DDD-YYYY');
-- interpreted as 31 December 2003, 12:59:33
SELECT TO_TIMESTAMP('2009-12-24 11:00:00 PM','YYYY-MM-DD HH12:MI:SS AM');
-- interpreted as 24 December 2009, 23:00:00
SELECT TO_TIMESTAMP('2000_MAY_12 10.123','YYYY_MONTH_DD SS.FF3');
-- interpreted as 12 May 2003, 00:00:10.123

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. For example, if the default value of NLS_TIMESTAMP_FORMAT is set to FF6, the number will be truncated to 6 fractional digits: 

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF6';

The following condition then evaluates to false:

SELECT '1111-11-11 11:11:11.123456789' = TIMESTAMP '1111-11-11 11:11:11.123456789';

Numeric format models

Numeric format models specify the interpretation or presentation of strings as numbers and vice versa. Numeric format models can be used in the functions TO_CHAR (number) and TO_NUMBER, and in the ETL commands IMPORT and EXPORT.

The following table lists the elements that can be used in numeric format models.

Element Example Description
Digit
9 9999,999 Each 9 stands for a digit. If the number has fewer digits than the format model, it is padded with leading spaces.
0 0000,000 Each 0 (zero) stands for a digit. If the number has fewer digits than the format model, it is padded with zeros.
Sign
S

S9999,999

9999,999S

Returns '+' for positive numbers and '-' for negative numbers.

S can be placed at either the beginning or the end of the format string.
MI 9999,999MI

Returns a space character for positive numbers and '-' for negative numbers.

MI must be placed at the end of the format string.
FM FM9999,999

Removes all superfluous zeros. Returns '-' for negative numbers and removes all leading and trailing spaces for positive numbers.

FM must be placed at the beginning of the format string.

Decimal separator
. 999.999 Returns a dot (period) as separator 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 specified 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, with A–F in uppercase. 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, with a–f in lowercase. 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

Usage notes

  • If the format model is shorter than the number, the returned string is padded with the character #.
  • The group and decimal separators are defined in NLS_NUMERIC_CHARACTERS. The separators can be changed using ALTER SESSION (for the current session) or ALTER SYSTEM (system-wide). The default decimal separator is a dot (.) and the default group separator a comma (,).
  • You can find the current values for the NLS parameters in the EXA_PARAMETERS system table.
Examples

The following examples show the use of numeric format models in the 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 decimal numbers
-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 (uppercase)
123 xxx '7b' Hexadecimal strings (lowercase)
123.4567 9.99eeee '1.23e2' Scientific notation
123.4567 9.99EEEE '1.23E2' Scientific notation