1、字符串转日期函数,str_to _date函数
<select id="getAdsInBar" resultType="cn.synron.kiosk.bean.AdBean">
select t_ad.*,count(t_ad_device.device_id) as deviceAmount,count(t_ad_file.file_id) as fileAmount
from t_ad left outer join t_ad_device on t_ad.id = t_ad_device.ad_id left outer join t_ad_file on t_ad.id=t_ad_file.ad_id
where
`type` =#{type}
and status =#{status}
<if test="createdBy!=null">
and created_by =#{createdBy}
</if>
<if test="name!=null">
and `name` =#{name}
</if>
<if test="timeCreated!=null">
and time_created =#{timeCreated}
</if>
<if test="status==1">
and ( DATE_FORMAT(now(), '%Y-%m-%d') between str_to_date(begin_play_date, '%Y-%m-%d') and str_to_date(end_play_date, '%Y-%m-%d') )
and ( DATE_FORMAT(now(), '%H:%i') not between str_to_date(end_play_time, '%H:%i') and str_to_date('24:59', '%H:%i') )
</if>
</select>
str_to_date(begin_play_date, ‘%Y-%m-%d’)这里边,begin_play_date的格式要跟后面的参数一致,即用“-”还是用“/”分开时间戳,不然转化出来的会是null
2、日期格式化函数,date_format
DATE_FORMAT(now(), '%Y/%m/%d')
把now()函数的日期以 %Y-%m-%d 的格式输出,如下图: