, | |
. | |
$ | |
0, 9 | |
B | |
C | |
D, G | See NLS_NUMERIC_CHARACTERS |
EEE… | |
L | |
MI | |
PR | |
RN/rn | Uppercase/lowercase roman numbers (XXIV etc.) |
S | |
TM | |
U | |
V | |
X |
create table tq84_number_format (
fmt varchar2(20) not null,
id number generated always as identity primary key
);
insert into tq84_number_format (fmt) values ( '9999999999' );
insert into tq84_number_format (fmt) values ( '9999999999.999');
insert into tq84_number_format (fmt) values ( '9999999990.099');
insert into tq84_number_format (fmt) values ('FM9999999990.099');
insert into tq84_number_format (fmt) values ( 'S9999999990.099');
insert into tq84_number_format (fmt) values ( 'S0000000000.999');
insert into tq84_number_format (fmt) values ( 'S9999999999.999');
insert into tq84_number_format (fmt) values ('TMe' );
insert into tq84_number_format (fmt) values ('TM9' );
insert into tq84_number_format (fmt) values ('9.9EEEE' );
insert into tq84_number_format (fmt) values ('999,999,999' );
insert into tq84_number_format (fmt) values ('999,999,999.99' );
insert into tq84_number_format (fmt) values ('000,000,000.00' );
insert into tq84_number_format (fmt) values ('000,000,009.00' );
insert into tq84_number_format (fmt) values ('RN' );
insert into tq84_number_format (fmt) values ('XXXXXX' );
insert into tq84_number_format (fmt) values ('B99,999,999.00' );
insert into tq84_number_format (fmt) values ('99999V99' );
column n1 format a18
column n2 format a18
column n3 format a18
column n4 format a18
column n5 format a18
select
fmt,
'>' || to_char( 0.7 , fmt) || '<' n1,
'>' || to_char( 42 , fmt) || '<' n2,
'>' || to_char( 123.4 , fmt) || '<' n3,
'>' || to_char( 1222333.444 , fmt) || '<' n4,
'>' || to_char( -17.1717, fmt) || '<' n5
from
tq84_number_format
order by
id;
--
-- FMT N1 N2 N3 N4 N5
-- -------------------- ------------------ ------------------ ------------------ ------------------ ------------------
-- 9999999999 > 1< > 42< > 123< > 1222333< > -17<
-- 9999999999.999 > .700< > 42.000< > 123.400< > 1222333.444< > -17.172<
-- 9999999990.099 > 0.700< > 42.000< > 123.400< > 1222333.444< > -17.172<
-- FM9999999990.099 >0.7< >42.0< >123.4< >1222333.444< >-17.172<
-- S9999999990.099 > +0.700< > +42.000< > +123.400< > +1222333.444< > -17.172<
-- S0000000000.999 >+0000000000.700< >+0000000042.000< >+0000000123.400< >+0001222333.444< >-0000000017.172<
-- S9999999999.999 > +.700< > +42.000< > +123.400< > +1222333.444< > -17.172<
-- TMe >7E-01< >4.2E+01< >1.234E+02< >1.222333444E+06< >-1.71717E+01<
-- TM9 >.7< >42< >123.4< >1222333.444< >-17.1717<
-- 9.9EEEE > 7.0E-01< > 4.2E+01< > 1.2E+02< > 1.2E+06< > -1.7E+01<
-- 999,999,999 > 1< > 42< > 123< > 1,222,333< > -17<
-- 999,999,999.99 > .70< > 42.00< > 123.40< > 1,222,333.44< > -17.17<
-- 000,000,000.00 > 000,000,000.70< > 000,000,042.00< > 000,000,123.40< > 001,222,333.44< >-000,000,017.17<
-- 000,000,009.00 > 000,000,000.70< > 000,000,042.00< > 000,000,123.40< > 001,222,333.44< >-000,000,017.17<
-- RN > I< > XLII< > CXXIII< >###############< >###############<
-- XXXXXX > 1< > 2A< > 7B< > 12A6BD< >#######<
-- B99,999,999.00 > .70< > 42.00< > 123.40< > 1,222,333.44< > -17.17<
-- 99999V99 > 70< > 4200< > 12340< >########< > -1717<
drop table tq84_number_format;
to_char(date, 'fmt'), to_date(str, 'fmt') and nls_date_format. AD, A.D. | Evaluates to AD or BD, A.D. or B.D. |
AM or PM, A.M. or P.M. | Evaluates to AM or PM, A.M. or P.M. |
CC, SCC | Century, S: with leading spaces |
D, DD, DDD | Day of week (1…7), month (1…31) or year (1…366). The value returned by D depends on value of nls_territory, see here. |
DL, DS | Long (Saturday, December 30, 2006) and short (12/30/2006) date format |
DY, Dy, dy | Abbreviated name of day (char(3)) |
E, EE | Abbreviated, non-abbrevated name of era |
FFx | Number of digits with which fractions of a second are rendered. x = 1 … 9 (6?) |
FM | No leading or trailing blanks |
FX | Format exactly. Without fx, Oracle tries to parse the input string with some alternatives. to_date('280870', 'dd.mm.yy') parses 280870, to_date('280870', 'fxdd.mm.yy') throws ORA-01861: literal does not match format string. |
HH, HH12, HH24 | Hour of the day. 1…12 and 1…24 |
IW | Week of the year |
IYYY | 4 digit ISO year (What's the difference to YYYY?) |
IYY, IY, I | Last three, two, one digits of ISO year |
J | Julian day. Sp can be added to spell numbers. |
MI | Minute of the hour (0 … 59) |
MM, RM | Month as number (1 … 12), or roman numerals (I … XII) |
MONTH, Month, month | Name of the month (char(9)) |
MON, Mon, mon | Abbreviated name of the month (char(3)) |
Q | Quarter of the year |
RR, RRRR | Two, four digit year. Compare with YYYY |
SS, SSSSS | Seconds of the hour, since midnight. See this example |
TS | Short time format |
TZD | Daylight saving information |
TZH, TZM | Time zone: hour, minute |
TZR | Time zone: region (for example Europe/Zurich or +03:00). |
WW, W | Week of year, of month |
X | Local radix character (which separates seconds from fractions of seconds) |
YYYY, YYY, YY, Y | Four digit year, last three, last two, last one digit of year. |
Y,YYY | year with thousand separator (who uses this?) |
YEAR, SYEAR | Year spelled out, negative years with minus sign |
nls_territory: alter session set nls_territory='Switzerland'; select to_char(date '2021-01-01','D') from dual; -- -- 5 alter session set nls_territory='America'; select to_char(date '2021-01-01','D') from dual; -- -- 6
trunc(dt, 'Day'): alter session set nls_territory = 'switzerland'; select trunc(sysdate, 'day') from dual; alter session set nls_territory = 'america'; select trunc(sysdate, 'day') from dual;
FM suppresses padding a result with leading or trailing blanks and zeroes. FM is a toggle: mulitple FM instructions can be included in a format each of which reverses the previous FM effect. (Oracle's docu says: «suppresses leading zeroes for subsequent elements»).