查询星期几:
select to_char(sysdate,'day') from dual;
查询几号:
select to_char(sysdate,'dd') from dual;
查询小时数:
select to_char(sysdate,'hh24') from dual;
查询时间:
select to_char(sysdate,'hh24:mi:ss') from dual;
查询日期时间:
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
查询星期几:
select to_char(to_date('2006-11-21','YYYY-MM-DD'),'DAY') from dual;
select to_char(to_date('2006-11-21','YYYY-MM-DD'),'DAY','NLS_DATE_LANGUAGE = American') from dual;
alter session set nls_date_language = 'simplified chinese';
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
查询两个日期间的天数:
FLOOR:返回等于或小于x的最大整数:
select floor(to_date('20061121','yyyymmdd') - to_date('20061120','yyyymmdd')) from dual;
//获取小时
SELECT EXTRACT(HOUR FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;
//获取月份
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
//计算一年多少天
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual;
//注意：trunc(sysdate,'year')截取生成==>'2006-01-01';
select trunc(sysdate,'year') from dual;
//查询一年中的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual;
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
select next_day(to_date('2006-11-21','YYYY-MM-DD'),6);
//返回一个月份的最后一天
Select last_day(date'2006-02-01') D from dual；
Select last_day(to_date('2006-02-01','YYYY-MM-DD')) from dual;
//返回一个月份的最后一天是星期几
select to_char(last_day(to_date('2006-11-21','YYYY-MM-DD')),'DAY') from dual;
//获取上个月末天：
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;
==>注意:last_day(sysdate)表示sysdate这个月的最后一天
add_months(last_day(sysdate),-1)表示上个月的最后一天
//上月今天
select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;
//上月首天
select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;
按照每周进行统计
select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');
按照每月进行统计
select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');
按照每季度进行统计
select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');
按照每年进行统计
select to_char(sysdate,'yyyy') from dual group by to_char(sysdate,'yyyy');
