情况1：从前端接收到字符串类型的日期
<if test
="startTime!=null and startTime.trim() neq ''">
and
date_format(p
.create_time
,'%Y-%m-%d %H:%i:%s') >
;= str_to_date(#
{startTime
},'%Y-%m-%d %H:%i:%s')
</if>
<if test
="endTime!=null and endTime.trim() neq ''">
and
date_format(p
.create_time
,'%Y-%m-%d %H:%i:%s') <
;= str_to_date(#
{endTime
},'%Y-%m-%d %H:%i:%s')
</if>
或者
<if test
="beginTime != null and beginTime !='' ">
and p
.create_time
<![CDATA
[>=]]>#
{beginTime
}
</if>
<if test
="endTime != null and endTime !=''">
and
<![CDATA
[p
.create_time
<=DATE_ADD(#
{endTime
},interval
1 day
)]]>
</if>
情况2：从前端接收到Date类型
<if test
="beginTime !=null">
<![CDATA
[and
DATE_FORMAT(t1
.create_Time
,'%Y-%m-%d')<=DATE_FORMAT(#
{beginTime
},'%Y-%m-%d')]]>
</if>
<if test
="endTime !=null">
<![CDATA
[and
DATE_FORMAT(t1
.create_Time
,'%Y-%m-%d')>=DATE_FORMAT(#
{endTime
},'%Y-%m-%d')]]>
</if>
情况3：从前端接收到字符串类型的日期，结合MP
List
<CustomerIntegral> customerIntegrals
= customerIntegralService
.list(new QueryWrapper<CustomerIntegral>().
apply(StringUtils
.isNotEmpty(todayString
),"date_format (create_time,'%Y-%m-%d') >= date_format('" + todayString
+ "','%Y-%m-%d')").
apply(StringUtils
.isNotEmpty(tomorrowString
),"date_format (create_time,'%Y-%m-%d') <= date_format('" + tomorrowString
+ "','%Y-%m-%d')"
));
打印出的SQL
SELECT
customer_integral_id
,
customer_data_id
,
integral
,
update_id
,
change_status
,
create_id
,
create_time
,
update_time
,
consumption_reasons
FROM
customer_integral
WHERE
date_format
( create_time
, '%Y-%m-%d' ) >= date_format
( 'yyyy-MM-dd', '%Y-%m-%d' )
AND date_format
( create_time
, '%Y-%m-%d' ) <= date_format
( 'yyyy-MM-dd', '%Y-%m-%d' )
这个要注意的是todayString和tomorrowString 必须是格式化的字符串类型 如： yyyy-MM-dd hh:mm:ss
具体场景可以选用具体情况，一般是传字符串比较好处理。
