to_date转换不了，下面是高手教的。

select count(distinct sidnumber) as counter from fd_contractdetail where TO_CHAR(CAST(TO_TIMESTAMP(DUPDATETIME,'dd-MON-yy hh:mi:ss.ff AM') as date),'yyyy-mm-dd') BETWEEN '2018-04-01 00:00:00' AND '2019-04-21 23:59:59';

select count(distinct sidnumber) as counter from fd_contractdetail where DUPDATETIME BETWEEN TO_TIMESTAMP('2018-04-01 00:00:00' ,'yyyy-mm-dd hh24:mi:ss')  AND TO_TIMESTAMP('2019-04-21 23:59:59','yyyy-mm-dd hh24:mi:ss')

两种模式都可以。