日期格式无法识别 oracle,Oracle时间统计 - 不以物喜-不以己悲的个人空间 - OSCHINA - 中文开源技术交流社区...

select count(*) from Member_user where to_char(CREATEDATE,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd'); --今天

select count(*) from Member_user where to_char(CREATEDATE,'dd')=to_char(sysdate,'dd'); --当天，所有月份的这个号

select count(*) from Member_user where to_char(CREATEDATE,'mm-dd')=to_char(sysdate,'mm-dd');

select count(*) from Member_user where to_char(CREATEDATE,'mm.dd')=to_char(sysdate,'mm.dd'); --当月当天，所有年的这一天

select count(*) from Member_user where to_char(CREATEDATE,'yyyy-iw')=to_char(sysdate,'yyyy-iw'); --本周

select count(*) from Member_user where to_char(CREATEDATE,'iw')=to_char(sysdate,'iw'); --当周，所有年的这一天

select count(*) from Member_user where to_char(CREATEDATE,'yyyy-mm')=to_char(sysdate,'yyyy-mm'); --本月

select count(*) from Member_user where to_char(CREATEDATE,'mm')=to_char(sysdate,'mm'); --当月

select count(*) from Member_user where to_char(CREATEDATE,'yyyy-q')=to_char(sysdate,'yyyy-q'); --本季度

select count(*) from Member_user where to_char(CREATEDATE,'q')=to_char(sysdate,'q'); --当季度

SELECT NEXT_DAY (SYSDATE,1) FROM dual; --下个星期日

SELECT NEXT_DAY (SYSDATE,2) FROM dual; --下个星期一

SELECT NEXT_DAY (SYSDATE,7) FROM dual; --下个星期六

SELECT NEXT_DAY (SYSDATE,8) FROM dual; --ORA-01846: 周中的日无效 not a valid day of the week

SELECT NEXT_DAY (SYSDATE,'星期一') FROM dual; --下个星期一

SELECT NEXT_DAY (SYSDATE,'Monday') FROM dual; --ORA-01846: 周中的日无效 not a valid day of the week

--2017-11-17

SELECT TO_CHAR(SYSDATE,'YYYY') FROM dual; -- 2017

SELECT TO_CHAR(SYSDATE,'YYY') FROM dual; --017

SELECT TO_CHAR(SYSDATE,'YY') FROM dual; --17

SELECT TO_CHAR(SYSDATE,'Y') FROM dual; --7

SELECT TO_CHAR(SYSDATE,'MM') FROM dual; --11

SELECT TO_CHAR(SYSDATE,'M') FROM dual; --ORA-01821: 日期格式无法识别 date format not recognized

SELECT TO_CHAR(SYSDATE,'RM') FROM dual; --2017-11-17，XI，即11；2018-04-11，IV，即4。

--罗马数字的符号一共只有7个：I(代表1)、V(代表5)、X(代表10)、L(代表50)、C代表100)、D(代表500)、M(代表1,000)。

SELECT to_char(sysdate,'dd') FROM dual; -- 2017/11/20 20

SELECT to_char(sysdate,'d') FROM dual; --星期日~星期六 1~7

SELECT TO_CHAR(SYSDATE,'Q') FROM dual; -- 第几季度

select last_day(sysdate) from dual; --该月最后一天

select months_between(to_date('2018.04.11','yyyy.mm.dd'),to_date('2017.11.20','yyyy.mm.dd')) mon_betw from dual; --4.70967741935483870967741935483870967742

Select to_char(Sysdate, 'YYYY-MM-DD') From Dual;

Select to_char(Sysdate, 'hh24:mi:ss') From Dual;

select to_char(trunc(sysdate),'YYYY-MM-DD hh24:mi:ss') from dual; --默认截取到日(当日的零点零分零秒)

select to_char(trunc(sysdate,'dd'),'yyyymmdd hh24:mi:ss') from dual; --截取到日(当日的零点零分零秒)

select trunc(sysdate) from dual; --默认截取到日(当日的零点零分零秒)

Select trunc(Sysdate, 'YYYY-MM-DD') From Dual; -- ORA-01898: 精度说明符过多 too many precision specifiers

Select trunc(Sysdate, 'MM') From Dual; -- 2017-11-21 结果： 2017-11-01

Select trunc(Sysdate, 'month') From Dual; -- 2017-11-21 结果： 2017-11-01

Select trunc(Sysdate, 'YYYY') From Dual; -- 2017-11-21 结果： 2017-01-01

select trunc(sysdate, 'year') from dual; -- 2017-11-21 结果： 2017-01-01

Select trunc(Sysdate, 'DD') From Dual; -- 2017-11-21 结果： 2017-11-21

select trunc(sysdate,'q') from dual; --截取到季度(本季度的第一天)

select trunc(sysdate,'hh24') from dual; --截取到小时(当前小时，零分零秒)

select trunc(sysdate,'mi') from dual; --截取到分(当前分，零秒)

select trunc(sysdate,'ss') from dual ;--ORA-01899: 精度说明符错误 bad precision specifier

select trunc(sysdate,'ww') from dual; --本周第2天，即本周一

select trunc(sysdate,'iw') from dual; --本周第2天，即本周一

select trunc(sysdate,'w') from dual; --本周第1天，即上周日

select trunc(sysdate-4,'w') from dual; --(sysdate-n)那一周第1天

select trunc(sysdate,'day') from dual; --本周第1天，即上周日

select sysdate - interval '7' day from dual; --当前时间减去7天的时间

select to_date(to_char(sysdate-7,'yyyy/mm/dd'),'yyyy/mm/dd') from dual;

select to_date(to_char(sysdate-7,'yyyy-mm-dd'),'yyyy-mm-dd') from dual;

--近一个月 每天登陆用户数目

select login_type, to_char(logindate,'yyyy-mm-dd'), count(*) from member_user_login_trace

where logindate > add_months(sysdate, -1)

group by login_type, to_char(logindate,'yyyy-mm-dd');

--这个月的每一天

SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'));

--今天以后的30天 包括今天

SELECT SYSDATE + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'));

SELECT SYSDATE + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= 30;

--2018-04-11 以后的30天 包括今天

with t as

(select rownum-1 rn from dual connect by rownum<=30)

select to_date('2018-04-11','yyyy-mm-dd')+rn from t ;

-- 2015-01-01 到 2015-01-15的每一天

with t as

(select rownum-1 rn from dual connect by rownum<=100)

select to_date('2015-01-01','yyyy-mm-dd')+rn from t

where to_char(to_date('2015-01-01','yyyy-mm-dd')+rn,'yyyy-mm-dd') between '2015-01-01' and '2015-01-15' ;

select TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd')) FROM DUAL; --30

select ROWNUM FROM DUAL; --1

select rownum-1 rn from dual connect by rownum<=30; -- 0~29

select * from v$version;