1. 报错:ORA-01841: (完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0
2. 我的sql是:
<!-- 查询 推荐下载记录 -->
<select id="selectAllRecommendRecord" parameterType="RecommendRecord" resultMap="RecommendRecordResultMap">
SELECT
distinct(theUser.ACCOUNT) AS ACCOUNT,
theUser.id AS ID,
theUser.REGISTER_DATE AS REGISTER_DATE,
theUser.PROVINCE AS PROVINCE,
theUser.REGIOIN AS REGIOIN,
record.CREATE_DATE,
product.FORMAT AS FORMAT,
record.STATUS,
record.IS_NOTIFY,
record.DOWNLOAD_COUNT
FROM TB_RECOMMEND_RECORD record ,TB_USER theUser ,TB_RECOMMEND_PRODUCT product
<where>
record.USER_ID = theUser.ID
AND record.RECOMMEND_ID = product.ID
<if test="account != null and account != ''">
AND theUser.ACCOUNT LIKE '%'||#{account}||'%'
</if>
<if test="regioin != null and regioin != ''">
AND theUser.regioin LIKE '%'||#{regioin}||'%'
</if>
<if test="province != null and province != ''">
AND theUser.province LIKE '%'||#{province}||'%'
</if>
<if test="status != null and status != '' and status.code != null and status.code != ''" >
AND record.STATUS = #{status.code}
</if>
<if test="startDate != null and startDate != ''">
AND record.CREATE_DATE >= TO_DATE(#{startDate},'yyyy-mm-dd hh24:mi:ss')
</if>
<if test="endDate != null and endDate != ''">
AND record.CREATE_DATE <![CDATA[ <= ]]> TO_DATE(#{endDate},'yyyy-mm-dd hh24:mi:ss')
</if>
</where>
GROUP BY ACCOUNT ,theUser.REGISTER_DATE, PROVINCE,REGIOIN, record. CREATE_DATE, FORMAT, STATUS, IS_NOTIFY, DOWNLOAD_COUNT, theUser.id
ORDER BY DOWNLOAD_COUNT DESC
</select>
3. 我的年份本身没有问题,问题是startDate和endDate 这2个参数。本来是在页面上作了时间条件查询,后来不要了,我忘记了去掉sql中对时间的过滤。
于是传给sql的startDate和endDate 的值都是undefine。就报这个错了。我这情况去掉这2个时间过滤,这个sql就OK 了。