Hi

I have a native sql query with count(*) in which I have datetime criteria converted using MS SQL function Convert(datetime..) while in debug I see the query properly formatted in "Query object" but as soon as I used list() function I get exception saying "[SQLServer JDBC Driver][SQLServer]Conversion failed when converting datetime from character string.". So If I check my Jboss logs I see my query has extra "?" in date string

my original query before executing ( I have my where clause ready in String already)

SELECT count(*) as mycount FROM rptdepositSummary
WHERE depClassId = 8 AND bankid = 2600000000001 AND depStatus IN (3,6,7)
AND (createDate BETWEEN Convert(datetime ,'2010-December-13 05:00:00')
AND Convert(datetime ,'2011-January-06 04:59:59.999'))

And After (in jboss logs)

SELECT count(*) as mycount FROM rptdepositSummary
WHERE depClassId = 8 AND bankid = 2600000000001 AND depStatus IN (3,6,7)
AND (createDate BETWEEN Convert(datetime ,'2010-December-13 05?')
AND Convert(datetime ,'2011-January-06 04?'))

My code is as below

Query query = HibernateUtility.currentSession().createSQLQuery(queryString);
((SQLQuery) query).addScalar("mycount", Hibernate.INTEGER);
Collection<?> results = (Collection<?>) query.uniqueResult();
int size = Integer.parseInt(results.iterator().next().toString());

Im using Hibernate 3.

Any help is appreciated

looks like im running into this escape sequence issue for named paramenters where Hibernate is thinking in time (HH:MM:SS) it adds "?" and chop off the rest of the value after first occurrence of ":"

found this viewtopic.php?p=2249465

not sure the last solution works or not but will give a try