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 ‘%/%’
