TO_DATE Format Masks | |
Format | Returns |
D | Day of the week |
DD | Day of the month |
DDD | Numerical day of the year, 1 ~ 365 (366 for Leap years) |
DAY | Full textual representation of the day, i.e. "Monday", "Tuesday", "Wednesday" |
DY | Day in three letters, i.e. "MON", "TUE", "FRI" |
W | Week of the month |
WW | Week of the year |
MM | Month in two digits, i.e. 01 = Jan, 02 = Feb,...12 = -Dec |
MON | Month in three characters, i.e. "Jan", "Feb", "Apr" |
MONTH | Full textual representation of the Month, i.e. "January", "February", "April" |
RM | Month in Roman Characters (I-XII, I-Jan, II-Feb, ... XII-Dec) |
Q | Quarter of the Month |
YY | Last two digits of the year. |
YYYY | Full year |
YEAR | Year in words like "Nineteen Eighty Seven" |
HH | Hours in 12 hour format |
HH12 | Hours in 12 hour format |
HH24 | Hours in 24 hour format ("military time") |
MI | Minutes |
SS | Seconds |
FF | Fractional Seconds |
SSSSS | Milliseconds |
J | Julian Day i.e Days since 1st-Jan-4712BC to till-date |
RR | If the year is less than 50 then Oracle considers the year as a 21st century date. If the year is greater than 50 then Oracle considers the year to be in the 20th century. |
字符串转变成date
SQL> SELECT TO_DATE('2014-4-10 14:10:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_DATE('2014-4-1014:10:00','Y
------------------------------
2014-4-10 14:10:00
获得当前时间
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------------------
2014-4-10 10:28:33
格式化时间
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2014-04-10 10:22:43
SQL>
12小时制
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH2
------------------------------
2014-04-10 13:24:03
SQL> SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH:
------------------------------
2014-04-10 01:24:06
SQL>