########### 日期函数 ###########
select now(); — 当时 yyyy-mm-dd hh:mm:ss
select curdate(); — 当天 yyyy-mm-dd
select (curdate() – interval (weekday(curdate()) + 1) day); — 本周第一天
select (curdate() – interval (day(curdate()) – 1) day); — 本月第一天
select (curdate() – interval (dayofyear(curdate()) – 1) day); — 本年第一天
select (now() + interval 1 month); — 下个月今天
select last_day(curdate()); — 本月最后一天
select last_day(curdate()) + interval 1 day; — 下月第一天
select date_format(‘2014-07-08 23:59:59′,’%Y-%m-%d %H:%i:%s’
) — str to date
— 查找今天的发帖
1. CREATETIME >= date_format(now(),’%Y-%m-%d’) and CREATETIME <= date_format('2014-07-08 23:59:59','%Y-%m-%d %H:%i:%s')
2. date_format(CREATETIME,’%Y-%m-%d’) = LEFT(now(),10)
3. date_format(CREATETIME,’%Y-%m-%d’) = date_format(now(),’%Y-%m-%d’)
4. CREATETIME like CONCAT(date_format(now(), ‘%Y-%m-%d’), ‘%’)
5. CREATETIME >= curdate() — and CREATETIME <= now() — 首选
UNIX_TIMESTAMP(SUBSTR(FROM_UNIXTIME(r.REPAYMENT_TIME)FROM 1 FOR 10)) UNIX_TIMESTAMP(SUBSTR(NOW()FROM 1 FOR 10))
==>
r.REPAYMENT_TIME < UNIX_TIMESTAMP(curdate())
and r.REPAYMENT_TIME >= UNIX_TIMESTAMP(curdate() + interval 1 day)
########### 其他函数 ###########
TRUNCATE(X ,D )
ROUND(X ,D )
select 12000/4578, truncate(12000/4578, 10), truncate(12000/4578, 8), round(12000/4578, 8)
IF(expr1,expr2,expr3) –> case when expr1 then expr2 else expr3 end
IFNULL(expr1,expr2) –> case when expr1 is not null then expr1 else expr2 end
CONCAT(str1 ,str2 ,…) –> concat(‘%’, ? ‘%’)
select CONCAT_WS(‘|’, ‘1’,’2′,’3′)
转载请注明原文链接：MYSQL常用函数_MySQL
