mysql/oracle日期相关代码(汇总)

Oracle日期相关代码
select sysdate from dual;

获取当前年份: select extract(year from sysdate) from dual
获取当前月份: select extract(month from sysdate) from dual
获取当前日: select extract(day from sysdate) from dual
获取当前年月: select to_char(sysdate,‘yyyymm’) from dual
获取当前时间: select to_char(sysdate, ‘YYYYMMDDHH24MISS’) dqsj from dual
获取上个月: select to_char(add_months(trunc(sysdate),-1),‘yyyymm’) from dual

将日期字符串转为日期类型:
select to_date(‘2009-4-21 10:14:39’,‘yyyy-mm-dd hh:mi:ss’) from dual;
注意: 2边的格式要一致, 不然可能会报错

oracle获取字符串的长度
select length(‘123456789’) from dual
oracle截取字符串
substr(tjsj, 1, 4) = ‘2019’
select substr(2019123456,1,4) from dual
按拼音排序
order by nlssort(zb_gysxyb.gysmc, ‘NLS_SORT=SCHINESE_PINYIN_M’)

---

Mysql日期相关代码
日期差值: mysql
转化为天: select round(timestampdiff(minute, pbkssj, pbjssj)/60/24,2)
注意: timestampdiff会把minute后面的舍去/置为0, 所以不能用hour

获取当前年份: select year(now())
获取上一年: select year(now()) - 1
获取当前时间: select DATE_FORMAT(now(),’%Y%m%d%H%i%s’)
得到当前年、月、日: select date_format(now(),’%Y-%m-%d’);
得到当前月份: select month(now());
concat(year(now()),’-01-01’)//当前年份的第一天
concat(year(now()),’-12-31’)//当前年份的最后一天

mysql截取字符串 (注意: mysql不能用substr语法 !!! )
select left(2019123456, 4)

字段长度过滤
SELECT * FROM table WHERE char_length(description) > 100

字符串拼接
CONCAT(left(gfstjsj,4),’-’,substring(gfstjsj,5,2),’-’,right(gfstjsj,2))

字段不包含某字符 用not like
如: 不包含 “/” not like ‘%/%’