Mybatis查询日期范围

查询日期范围

将日期时间，转换为字符串

select
s.*
from BIZ_ASSAY_ORDER_SAMPLE s
LEFT JOIN BIZ_ASSAY_ORDER o on o.ID=s.ORDER_ID
WHERE 1=1
<if test="status !=null and status !=''">
    AND s.RECORD_STATUS=#{status}
</if>
<if test="sampleNo !=null and sampleNo !=''">
    AND s.SAMPLE_NO LIKE '%'||#{sampleNo}||'%'
</if>
<if test="orderNo !=null and orderNo !=''">
    AND o.ORDER_NO LIKE '%'||#{orderNo}||'%'
</if>
<if test="sampleBizModelId !=null and sampleBizModelId !=''">
    AND s.SAMP_BIZ_MODE_ID=#{ sampleBizModelId }
</if>
<if test="statusFlag !=null and statusFlag !=''">
    AND s.STATUS_FLAG=#{ statusFlag }
</if>
<if test="fromDate != null and fromDate !=''">
    and to_char(o.ORDER_DATE,'yyyy-MM-dd') &gt;=  #{fromDate}
</if>
<if test="toDate != null and toDate !=''">
    and to_char(o.ORDER_DATE,'yyyy-MM-dd') &lt;=  #{toDate}
</if>

关键代码

<if test="fromDate != null and fromDate !=''">
    and to_char(o.ORDER_DATE,'yyyy-MM-dd') &gt;=  #{fromDate}
</if>
<if test="toDate != null and toDate !=''">
    and to_char(o.ORDER_DATE,'yyyy-MM-dd') &lt;=  #{toDate}
</if>

或者

将字符串，转换为日期时间

SELECT
<include refid="Base_Column_List"/>
FROM BIZ_DAILY_PLAN dp
LEFT JOIN DIC_TEST_OBJECT tb ON dp.TEST_OBJECT_ID=tb.ID
LEFT JOIN DIC_SAMPLE_BIZ_MODEL bm ON dp.SAMP_BIZ_MODE_ID=bm.ID
LEFT JOIN RES_LABORATORY l ON dp.LABORATORY_ID=l.ID
WHERE 1=1 AND dp.RECORD_STATUS = ${@cn.com.hwasunsoft.lims.core.enums.RecordStatusEnum@VALID.getValue()}
<if test="dailyPlanStatus !=null and dailyPlanStatus !=''">
    AND dp.CONFIRMATION_STATUS=#{dailyPlanStatus}
</if>
<if test="status !=null and status !=''">
    AND dp.RECORD_STATUS=#{status}
</if>
<if test="labId !=null and labId !=''">
    AND dp.LABORATORY_ID=#{labId}
</if>
<if test="fromDate !=null and fromDate !=''">
    AND dp.PLAN_DATE >= TO_DATE(#{fromDate},'yyyy-mm-dd')
</if>
<if test="toDate !=null and toDate !=''">
    AND TO_DATE(#{toDate},'yyyy-mm-dd') >= dp.PLAN_DATE
</if>

关键代码

<if test="fromDate !=null and fromDate !=''">
    AND dp.PLAN_DATE >= TO_DATE(#{fromDate},'yyyy-mm-dd')
</if>
<if test="toDate !=null and toDate !=''">
    AND TO_DATE(#{toDate},'yyyy-mm-dd') >= dp.PLAN_DATE
</if>

日期等于某天
直接把大于、小于号，改为等于号