Oracle获取毫秒和年月日到毫秒方法

SELECT TO_CHAR((SYSDATE - TO_DATE('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') - (1 / 3)) * 8640000, '999999999999999') FROM DUAL;
select (to_char(systimestamp, 'yyyymmddhh24missff2 ')) from dual;

 

 

1.查询当前月数据

select * from xpmt_coupon  x where  to_char(sysdate, 'yyyy-mm' )!=to_char(x.created_date,'yyyy-mm');

2.查询上一个月的数据
select * from xpmt_coupon  x where to_char(add_months(trunc(sysdate),-2),'yyyy-mm')=to_char(x.created_date,'yyyy-mm');

3.判断字段是否为数字，如果是则是TRUE，如果不是则是false

select * from xprd_brand x where regexp_like(x.initial_info,'^[[:digit:]]+$') order by x.initial_info;

4.数字加字符字段的排序方式

select * from xprd_brand x  order by regexp_replace(regexp_replace(x.initial_info,'(\d{1,2})([A-Z]{1})(\d{1,2})','0\1\20\3'),'0(\d{2})','\1')