data 转为timstam可以函数to_timestamp的方式来转化
Select to_timestamp('2018-02-27 09:48:28','yyyy-mm-dd hh24:mi:ss.ff') From dual;
select cast(to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') as date) timestamp_to_date from dual;
--timestamp转换为date(ts字段为timestamp类型) SELECT cast(ts AS DATE) from tab1 WHERE tid=1;
--timestamp转换为date(ts字段为timestamp类型) SELECT cast(ts AS DATE) from tab1 WHERE tid=3;
--date相减 SELECT (SELECT cast(ts AS DATE) from tab1 WHERE tid=3)-(SELECT cast(ts AS DATE) from tab1 WHERE tid=1) FROM dual;
--把date转换为妙
SELECT ((SELECT cast(ts AS DATE) from tab1 WHERE tid=3)-(SELECT cast(ts AS DATE) from tab1 WHERE tid=1))*24*60*60 FROM dual;
SELECT * from DATA_QUERY tt where cast(tt.collect_date AS DATE) > to_date('2018-02-02 00:00:00','yyyy/mm/dd hh24:mi:ss')
SELECT * from DATA_QUERY tt where to_number(to_char(cast(tt.collect_date AS date ),'yyyymmdd')) > 20180202
--------------------------------------------------------------------------------
保存的SQL,函数nvl(),count(),case用法的例子：
select t.dic_uuid DIC_UUID, t.software_name||' '|| t.software_version SOFTWARE_NAME , count(1) INSTALL_NUMBER ,
nvl((select p.ASSIGN_NUM from AA p where p.assign_dept = t.dept_no and p.dic_id = t.dic_uuid and p.enabled_flag = 'Y'),0) ASSIGN_NUMBER,
count(case when t.compliance_flag = 'Y' then 1 end) as COMPLIANCE_NUMBER,
count(case when t.compliance_flag = 'N' then 1 end) as NOCOMPLIANCE_NUMBER
from BB t
where t.compliance_flag != 'S' and t.dept_no = '004030902' and t.collect_date > sysdate - 30
group by t.dept_no,t.dic_uuid,t.software_name,t.software_version