今天星期几
select to_char(sysdate,'DY') from dual;
select to_char(systimestamp,'day' ) from dual
今天是本周第几天
select to_char(sysdate,'D') from dual;
select to_char(systimestamp,'d') from dual;
当前时间转换中文
select to_date(to_char(sysdate,'dd-mon-yy'),'dd-Mon-yy','NLS_LANGUAGE = CHINESE.ZHS16GBK') from dual
月份差
select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL
时间比较
当时时间在2008年11月1日至2008年12月31日之间
select sysdate from dual where trunc(sysdate) between to_date('2008-11-01','yyyy-mm-dd') and to_date('2008-12-31','yyyy-mm-dd')
(sysdate >= to_date('2007-09-01', 'yyyy.mm.dd') AND sysdate< to_date('2007-09-02', 'yyyy.mm.dd'))
比较时间是否相等
trunc(sysdate) = to_date('2008-12-08','yyyy-mm-dd');
to_char(sysdate,'yyyy-mm-dd') = '2008-12-08';
填加月份
select add_months(sysdate,12) from dual
extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
select extract(month from sysdate) "This Month" from dual;
select extract(year from add_months(sysdate,36)) "3 Years Out" from dual;
localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;