My colleagues who are much better at Hibernate than I, have tried to help me with this question but I still haven't gotten it to work. Some background on the problem:

We have a table where we declare a datetime field as a varchar in the following manner (This is a standard for our project so I cannot change it):

CREATE TABLE MY_TABLE (
CC CHAR(2) NOT NULL,
STATUS CHAR(1) NOT NULL,
DATETIME_LAST_CHG VARCHAR(14) DEFAULT CONVERT( char(8), GETDATE(), 112 ) + substring( CONVERT(char(8), GETDATE(), 8), 1, 2) + substring( CONVERT(char(8), GETDATE(), 8), 4, 2) NOT NULL
)

I am trying to run the following Hibernate query:

String query = "SELECT cc, status, COUNT(status) FROM MY_TABLE WHERE DATETIME_LAST_CHG >= :datetimeLastChg GROUP BY cc, status";

List<Object> results = entityManager.createNativeQuery(query)
.setParameter("datetimeLastChg", "20081025120000").getResultList();



When I run this, I get back no results even though there are 6 records that work. I turned on Hibernate logging, took the query that it says that it ran, put it into a another SQL tool and I get the six records that I'm looking for. If I take out the datetime check, I get all of the records as expected. If I remove the GROUP BY clause, I also get back every record instead of the six that I should get. I've tried everything that I can think of and I either get everything or nothing. I can't get it to only return records since a certain date. Any ideas?

I finally solved it. Basic stupidity. SQL tests were pointed at one test database and Hibernate was pointing at a different one. The Hibernate version all had the same datetime stamps.