Data Masker for Oracle 5

The Formatting Codes for the Date-as-Text Dataset

The Sequential Dates Dataset Form

The two date-as-text datasets can produce dates in any format - however a specialized format code must be provided. In the example above, the date format code is yyyyMMdd HH:mm:ss and this will produce dates (as text) in a format like 20100521 11:23:34. The formatting codes used are the standard Oracle TO_CHAR date format model. For the convenience of those users who do not have the Data Masker software installed on an Internet connected PC, the table below reproduces the date formatting codes listed in the Oracle documentation. These can be combined to produce any desired date format.

ElementDescription
-
/
,
.
;
:
"text"

Punctuation and quoted text is reproduced in the result.

AD
A.D.

AD indicator with or without periods.

AM
A.M.

Meridian indicator with or without periods.

BC
B.C.

BC indicator with or without periods.

CC
SCC

Century.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.

  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D

Day of week (1-7).

DAY

Name of day, padded with blanks to display width of the widest name of day in the date language used for this element.

DD

Day of month (1-31).

DDD

Day of year (1-366).


Returns a value in the long date format, which is an extension of Oracle Database's DATE format (the current value of the NLS_DATE_FORMAT parameter). Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy'.

Restriction: You can specify this format only with the TS element, separated by white space.


Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGEparameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'MM/DD/RRRR'. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format 'DD/MM/RRRR'.

Restriction: You can specify this format only with the TS element, separated by white space.

DY

Abbreviated name of day.

E

Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).

EE

Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).

FF [1..9]

Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime datatype or the datatype's default precision.

Examples: 'HH:MI:SS.FF'

SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;

FM

Returns a value with no leading or trailing blanks.

FX

Requires exact matching between the character data and the format model.

HH

Hour of day (1-12).

HH12

Hour of day (1-12).

HH24

Hour of day (0-23).

IW

Week of year (1-52 or 1-53) based on the ISO standard.

IYY
IY
I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard.

J

Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.

MI

Minute (0-59).

MM

Month (01-12; January = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to display width of the widest name of month in the date language used for this element.

PM
P.M.

Meridian indicator with or without periods.

Q

Quarter of year (1, 2, 3, 4; January - March = 1).

RM

Roman numeral month (I-XII; January = I).

RR

Lets you store 20th century dates in the 21st century using only two digits.

RRRR

Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.

SS

Second (0-59).

SSSSS

Seconds past midnight (0-86399).


Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.

Restriction: You can specify this format only with the DL or DS element, separated by white space.

TZD  

Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR.

Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).

TZH

Time zone hour. (See TZM format element.)

Example: 'HH:MI:SS.FFTZH:TZM'.

TZM

Time zone minute. (See TZH format element.)

Example: 'HH:MI:SS.FFTZH:TZM'.

TZR

Time zone region information. The value must be one of the time zone regions supported in the database.

Example: US/Pacific

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

X

Local radix character.

Example: 'HH:MI:SSXFF'.

Y,YYY

Year with comma in this position.

YEAR
SYEAR

Year, spelled out; S prefixes BC dates with a minus sign (-).

YYYY
SYYYY

4-digit year; S prefixes BC dates with a minus sign.

YYY
YY
Y

Last 3, 2, or 1 digit(s) of year.



Didn't find what you were looking for?