¨CMONTHS_BETWEEN(date2,date1)
¸ø³ödate2-date1µÄÔ·Ý
SQL> select months_between(¡¯19-12ÔÂ-1999¡ä,¡¯19-3ÔÂ-1999¡ä) mon_between from dual;
MON_BETWEEN
¡ª¡ª¡ª¨C
9
SQL>select months_between(to_date(¡¯2000.05.20¡ä,¡¯yyyy.mm.dd¡¯),to_date(¡¯2005.05.20¡ä,¡¯yyyy.dd¡¯)) mon_betw from dual;
MON_BETW
¡ª¡ª¡ª
-60
Oracle¼ÆËãʱ¼ä²î±í´ïʽ
¨C»ñÈ¡Á½Ê±¼äµÄÏà²îºÀÃëÊý
select ceil((To_date(¡¯2008-05-02 00:00:00¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯) ¨C To_date(¡¯2008-04-30 23:59:59¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯)) * 24 * 60 * 60 * 1000) Ïà²îºÀÃëÊý FROM DUAL;
/*
Ïà²îºÀÃëÊý
¡ª¡ª¡ª-
86401000
1 row selected
*/
¨C»ñÈ¡Á½Ê±¼äµÄÏà²îÃëÊý
select ceil((To_date(¡¯2008-05-02 00:00:00¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯) ¨C To_date(¡¯2008-04-30 23:59:59¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯)) * 24 * 60 * 60) Ïà²îÃëÊý FROM DUAL;
/*
Ïà²îÃëÊý
¡ª¡ª¡ª-
86401
1 row selected
*/
¨C»ñÈ¡Á½Ê±¼äµÄÏà²î·ÖÖÓÊý
select ceil(((To_date(¡¯2008-05-02 00:00:00¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯) ¨C To_date(¡¯2008-04-30 23:59:59¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯))) * 24 * 60) Ïà²î·ÖÖÓÊý FROM DUAL;
/*
Ïà²î·ÖÖÓÊý
¡ª¡ª¡ª-
1441
1 row selected
*/
¨C»ñÈ¡Á½Ê±¼äµÄÏà²îСʱÊý
select ceil((To_date(¡¯2008-05-02 00:00:00¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯) ¨C To_date(¡¯2008-04-30 23:59:59¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯)) * 24) Ïà²îСʱÊý FROM DUAL;
/*
Ïà²îСʱÊý
¡ª¡ª¡ª-
25
1 row selected
*/
¨C»ñÈ¡Á½Ê±¼äµÄÏà²îÌìÊý
select ceil((To_date(¡¯2008-05-02 00:00:00¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯) ¨C To_date(¡¯2008-04-30 23:59:59¡ä , ¡®yyyy-mm-dd hh24-mi-ss¡¯))) Ïà²îÌìÊý FROM DUAL;
/*
Ïà²îÌìÊý
¡ª¡ª¡ª-
2
1 row selected
*/
¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª-
×¢£ºÌìÊý¿ÉÒÔ2¸öÈÕÆÚÖ±½Ó¼õ£¬ÕâÑù¸ü¼Ó·½±ã
¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª-
¨C»ñÈ¡Á½Ê±¼äÔ·ݲî
select (EXTRACT(year FROM to_date(¡¯2009-05-01¡ä,¡¯yyyy-mm-dd¡¯)) ¨C EXTRACT(year FROM to_date(¡¯2008-04-30¡ä,¡¯yyyy-mm-dd¡¯))) * 12 +
EXTRACT(month FROM to_date(¡¯2008-05-01¡ä,¡¯yyyy-mm-dd¡¯)) ¨C EXTRACT(month FROM to_date(¡¯2008-04-30¡ä,¡¯yyyy-mm-dd¡¯)) months
from dual;
/*
MONTHS
¡ª¡ª¡ª-
13
1 row selected
*/
¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¨C
×¢£º¿ÉÒÔʹÓÃmonths_betweenº¯Êý£¬¸ü¼Ó·½±ã
¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¡ª¨C
¨C»ñÈ¡Á½Ê±¼äÄê·Ý²î
select EXTRACT(year FROM to_date(¡¯2009-05-01¡ä,¡¯yyyy-mm-dd¡¯)) ¨C EXTRACT(year FROM to_date(¡¯2008-04-30¡ä,¡¯yyyy-mm-dd¡¯)) years from dual;
/*
YEARS
¡ª¡ª¡ª-
1
select sysdate,add_months(sysdate,12) from dual; ¨C¼Ó1Äê
select sysdate,add_months(sysdate,1) from dual; ¨C¼Ó1ÔÂ
select sysdate,TO_CHAR(sysdate+7,¡¯yyyy-mm-dd HH24:MI:SS¡¯) from dual; ¨C¼Ó1ÐÇÆÚ
select sysdate,TO_CHAR(sysdate+1,¡¯yyyy-mm-dd HH24:MI:SS¡¯) from dual; ¨C¼Ó1Ìì
select sysdate,TO_CHAR(sysdate+1/24,¡¯yyyy-mm-dd HH24:MI:SS¡¯) from dual; ¨C¼Ó1Сʱ
select sysdate,TO_CHAR(sysdate+1/24/60,¡¯yyyy-mm-dd HH23:MI:SS¡¯) from dual; ¨C¼Ó1·ÖÖÓ
select sysdate,TO_CHAR(sysdate+1/24/60/60,¡¯yyyy-mm-dd HH23:MI:SS¡¯) from dual; ¨C¼Ó1Ãë
select sysdate+7 from dual; ¨C¼Ó7Ìì
½«µ±Ç°ÈÕÆÚת»»ÎªÉÏÒ»¸öÔÂ
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), ¡®yyyymm¡¯) ¨C»ñÈ¡µ±Ç°Ê±¼äµÄÇ°Ò»¸öÔÂ
FROM DUAL;
select sysdate from dual; /**»ñÈ¡µ±Ç°Ê±¼äµ½Ãë**/
select sysdate-3 from dual;/**»ñÈ¡µ±Ç°2Ìì**/
select round(sysdate) as ¸ñʽ³ÉÈÕÆÚ from dual;
select to_date(¡¯2008-9-2¡ä,¡¯yyyy_mm_dd¡¯) as ¸ñʽ³ÉÈÕÆÚ from dual;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), ¡®yyyymm¡¯) FROM DUAL; /**¨C»ñÈ¡µ±Ç°Ê±¼äµÄÇ°Ò»¸öÔÂ,ÕýÏòºó**/
Select last_day(sysdate) from dual;/**±¾ÔÂ×îÊÜÒ»Ìì**/
/***·Ö±ðȡʱ¼äµÄÄê¡¢Ô¡¢ÈÕ***/
Select to_char(sysdate,¡¯YYYY¡¯) from dual;
select to_char(sysdate,¡¯mm¡¯) from dual;
select to_char(sysdate,¡¯dd¡¯) from dual;