Hibernate version 3, Oracle DB.

Having problem with simple sql when using hibernate filter with parameter. If i take the generated sql, set the filter param string value into it and run the query manually, it runs fine without error. Also, if I run the same query and use a hibernate filter that does not have parameters, then the query also runs fine. Any ideas would be appreciated. Please let me know if you need more info.

--HQL:
--select count(patientTransfer.id) from PatientTransfer as patientTransfer where patientTransfer.effectivity.startDate is not null

--FILTER 1
--<filter-def name="effectivity" condition="nvl(effective_end,'01-JAN-1001')='01-JAN-1001'"/>

--FILTER 2
--<filter-def name="effectivityAsOf" condition=":asOfDateParam BETWEEN effective_start AND nvl(effective_end, to_date('01/01/3500','MM/DD/YYYY'))">
--<filter-param name="asOfDateParam" type="string"/>
--</filter-def>

--FILTER PARAM RUNTIME VAL = STRING
--{asOfDateParam=to_date('2006-12-20 21:56:00','YYYY-MM-DD HH24:MI:22')}

--RESULT USING FILTER 2: ERROR!!
--could not execute query [select count(patienttra0_.id) as col_0_0_ from PATIENT_TRANSFER patienttra0_ where :effectivityAsOf.asOfDateParam BETWEEN patienttra0_.effective_start AND nvl(patienttra0_.effective_end, to_date('01/01/3500','MM/DD/YYYY')) and ((patienttra0_.effective_start is not null ))]
--java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected

--RESULT USING FILTER 1: WORKS FINE
--select count(patienttra0_.id) as col_0_0_ from PATIENT_TRANSFER patienttra0_ where nvl(patienttra0_.effective_end,'01-JAN-1001')='01-JAN-1001' and ((patienttra0_.effective_start is not null ))

This works fine after fixing the small typo issue: 22 should've been SS.

However, now that this works fine, I am now trying my original query where I first encounted the problem - the more complex query includes a subquery. I did find the following post which refers to bug with something like what I am trying to do: http://forum.hibernate.org/viewtopic.ph ... ilterparam

--FILTER PARAM RUNTIME VAL = STRING
--{asOfDateParam=to_date('2006-12-20 21:56:00','YYYY-MM-DD HH24:MI:22')}[/url]