Eine Einführung in

SQL

Format a date and time.

Oracle
Many engines support the SQL standard - see Mimer for details.


Specific to Oracle
The function TO_CHAR together with the Datetime Format Elements is useful here:

Element

Specify in TO_* datetime functions?a

Meaning

-
/
,
.
;
:
"text"

Yes

Punctuation and quoted text is reproduced in the result.

AD
A.D.

Yes

AD indicator with or without periods.

AM
A.M.

Yes

Meridian indicator with or without periods.

BC
B.C.

Yes

BC indicator with or without periods.

CC
SCC

No

Century.

For example, 2002 returns 21; 2000 returns 20.

D

Yes

Day of week (1-7).

DAY

Yes

Name of day, padded with blanks to length of 9 characters.

DD

Yes

Day of month (1-31).

DDD

Yes

Day of year (1-366).

DY

Yes

Abbreviated name of day.

E

No

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

EE

No

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

FF [1..9]

Yes

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

HH

Yes

Hour of day (1-12).

HH12

No

Hour of day (1-12).

HH24

Yes

Hour of day (0-23).

IW

No

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

IYY
IY
I

No

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

IYYY

No

4-digit year based on the ISO standard.

J

Yes

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

MI

Yes

Minute (0-59).

MM

Yes

Month (01-12; JAN = 01).

MON

Yes

Abbreviated name of month.

MONTH

Yes

Name of month, padded with blanks to length of 9 characters.

PM
P.M.

No

Meridian indicator with or without periods.

Q

No

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

RM

Yes

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

RR

Yes

Lets you store 20th century dates in the 21st century using only two digits. See "The RR Date Format Element" for detailed information.

RRRR

Yes

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

SS

Yes

Second (0-59).

SSSSS

Yes

Seconds past midnight (0-86399).

TZD 

Yes

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

Yes

Time zone hour. (See TZM format element.)

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

TZM

Yes

Time zone minute. (See TZH format element.)

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

TZR

Yes

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

Example: US/Pacific

WW

No

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

No

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

X

Yes

Local radix character.

Example: 'HH:MI:SSXFF'.

Y,YYY

Yes

Year with comma in this position.

YEAR
SYEAR

No

Year, spelled out; "S" prefixes BC dates with "-".

YYYY
SYYYY

Yes

4-digit year; "S" prefixes BC dates with "-".

YYY
YY
Y

Yes

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