Oracle date

 参考网址:http://www.oradev.com/oracle_date_format.jsp

1、Oracle支持五种格式的分隔符:

  -/,.;:

SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS A,
       TO_CHAR(SYSDATE, 'yyyy/mm/dd') AS B,
       TO_CHAR(SYSDATE, 'YYYY,mm,dd') AS C,
       TO_CHAR(SYSDATE, 'yyyy:mm:dd') AS D,
       TO_CHAR(SYSDATE, 'yyyy;mm;dd')
  FROM DUAL;

2、常用的年的格式有以下几种:

YYYY: All four digits of the year

Y,YYY: All four digits of the year with a comma

YYY: Last three digits of the year

YY: Last two digits of the year

Y: Last digit of the year

3、常用的周格式:

WW: Two-digit week of the year:一年的第几个周

W: One-digit week of the month:本月的第几个周

4、常用的日格式:

DDD: Three-digit day of the year--今天是一年的第XXX天

DD:Two-digit day of the month   --今天是一个月中的第XXX天

D: One-digit day of the week    --今天是一个周中的第XXX天]

DAY/Day/DY/dy: 今天是周XXX,结果是中文的具体星期数

5、常用的小时格式

HH24: Two-digit hour in 24-hour format(24小时制)

HH: Two-digit hour in 12-hour format(12小时制)

6、常用的分钟和秒数格式

MI: Two-digit minute

SS: Two-digit second

7、常见的集中日期格式转换

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') AS A,
       TO_CHAR(SYSDATE, 'yyyy-mm-dd') AS B,
       TO_CHAR(SYSDATE, 'dd/MM/YYYY') AS C,
       TO_CHAR(SYSDATE, 'yyyy') || '年' || TO_CHAR(SYSDATE, 'mm') || '月' ||
       TO_CHAR(SYSDATE, 'dd') || '日' AS D
  FROM DUAL;

--假如查询的月是小于10月的则会出现:01月，02月，可以使用如下函数处理

SELECT DECODE(SUBSTR((TO_CHAR(SYSDATE, 'mm')), 1, 1), 
              '0',
              SUBSTR((TO_CHAR(SYSDATE, 'mm')), 2, 1) || '月')
  FROM DUAL

EXTRACT()函数的使用:

 EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression.Some combinations of datetime field and datetime or interval value expression result in ambiguity. In these cases, Oracle returns 'UNKNOWN'(当格式错误的时候，返回的UNKOWN)

 使用TO_DATE或者是在日期前面加上DATE关键字都可以进行日期格式的转换。

例如:

SELECT * FROM EMP E WHERE E.HIREDATE < DATE '2012-08-09'

---

SELECT EXTRACT(YEAR FROM TO_DATE('1998-03-07', 'YYYY-MM-DD')) AS A,
       EXTRACT(YEAR FROM DATE '1998-03-07') AS B
  FROM DUAL;

Oracle date format

With the functions to_char and to_date, a date format can be used. Example:
select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual;
will return something like: 24/03/2006 14:36:43

Here is a list of all the formats that can be used:

Format mask	Description
CC	Century
SCC	Century BC prefixed with -
YYYY	Year with 4 numbers
SYYY	Year BC prefixed with -
IYYY	ISO Year with 4 numbers
YY	Year with 2 numbers
RR	Year with 2 numbers with Y2k compatibility
YEAR	Year in characters
SYEAR	Year in characters, BC prefixed with -
BC	BC/AD Indicator *
Q	Quarter in numbers (1,2,3,4)
MM	Month of year 01, 02...12
MONTH	Month in characters (i.e. January)
MON	JAN, FEB
WW	Weeknumber (i.e. 1)
W	Weeknumber of the month (i.e. 5)
IW	Weeknumber of the year in ISO standard.
DDD	Day of year in numbers (i.e. 365)
DD	Day of the month in numbers (i.e. 28)
D	Day of week in numbers(i.e. 7)
DAY	Day of the week in characters (i.e. Monday)
FMDAY	Day of the week in characters (i.e. Monday)
DY	Day of the week in short character description (i.e. SUN)
J	Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH	Hournumber of the day (1-12)
HH12	Hournumber of the day (1-12)
HH24	Hournumber of the day with 24Hours notation (0-23)
AM	AM or PM
PM	AM or PM
MI	Number of minutes (i.e. 59)
SS	Number of seconds (i.e. 59)
SSSSS	Number of seconds this day.
DS	Short date format. Depends on NLS-settings. Use only with timestamp.
DL	Long date format. Depends on NLS-settings. Use only with timestamp.
E	Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE	The full era name
FF	The fractional seconds. Use with timestamp.
FF1..FF9	The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM	Fill Mode: suppresses blianks in output from conversion
FX	Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I	the last 3,2,1 digits of the ISO standard year. Output only
RM	The Roman numeral representation of the month (I .. XII)
RR	The last 2 digits of the year.
RRRR	The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC	Century. BC dates are prefixed with a minus.
CC	Century
SP	Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH	Spelled and ordinal format; 1 results in first.
TH	Converts a number to it's ordinal format. For example 1 becoms 1st.
TS	Short time format. Depends on NLS-settings. Use only with timestamp.
TZD	Abbreviated time zone name. ie PST.
TZH	Time zone hour displacement.
TZM	Time zone minute displacement.
TZR	Time zone region
X	Local radix character. In america this is a period (.)