Element
Specify in TO_DATE?
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
One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-". For example, '20' from '1900'.
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 Yes
Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE Yes
Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
HH Yes
Hour of day (1-12).
HH12 Yes
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
Two-digit numeric abbreviation of 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
Given a year with 2 digits: Returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50. Returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50.
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, enter the 4-digit year.
SS Yes
Second (0-59).
SSSSS Yes
Seconds past midnight (0-86399).
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.
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
Specify in TO_DATE?
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
One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-". For example, '20' from '1900'.
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 Yes
Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE Yes
Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
HH Yes
Hour of day (1-12).
HH12 Yes
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
Two-digit numeric abbreviation of 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
Given a year with 2 digits: Returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50. Returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50.
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, enter the 4-digit year.
SS Yes
Second (0-59).
SSSSS Yes
Seconds past midnight (0-86399).
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.
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
alter session set nls_language=american;也可以这样来查询日期是本周的第几天select to_char(sysdate,'d') from dual;SQL> select to_char(sysdate,'DAY','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') from dual;TO_CHA
------
星期四
同上，还可以这样写
select to_char(to_date('2002-09-24','yyyy-mm-dd'),'DAY','NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''') from dual;select to_char(v_data,'day') from dual;
eg: '20020926'=> v_data
default: “星期四”
alter session set nls_language=american;
result: thursdayselect to_char(v_data,'d') from dual;
result:5
regard sunday as the first day
又学到东西了。再给你几个有用的select to_char(sysdate,'yyyy') from dual; --年
select to_char(sysdate,'Q' from dual; --季
select to_char(sysdate,'mm') from dual; --月
select to_char(sysdate,'dd') from dual; --日
ddd 年中的第几天
WW 年中的第几个星期
W 该月中第几个星期
D 周中的星期几
hh 小时(12)
hh24 小时(24)
Mi 分
ss 秒