Hi,
My problem is incompatibility between java.sql.Date and SQL Server 'datetime' variable. Details follow:
I am using JBoss 3.2.3. I have an entity bean that has to search through a SQL Server table and find all those orders whose 'dateOrderPlaced' = some-supplied-date. I have written the query in EJB-QL like so:
<query-method>
<method-name>findNumberOfOrders</method-name>
<method-params>
<method-param>java.sql.Date</method-param>
</method-params>
</query-method>
<jboss-ql>[CDATA[ SELECT OBJECT(o) From POrder AS o
WHERE o.dateOrderPlaced = ?1
]]
</jboss-ql>
The argument that I supply above is in the java.sql.Date format. The code for that is given below
-----------------------------------------------------------------------------------------------------------
java.sql.Date sqlFromDate = new java.sql.Date((new Date()).getTime());
// where sqlFromDate is the argument to the EJB-QL above
-----------------------------------------------------------------------------------------------------------
SQL Server stores dates in a 'datetime' variable. When I run the application as above, I get a return value of 0 orders.
So I ran a query from a SQL Server client as follows
select dateorderplaced from orders
where datepart(yyyy,dateorderplaced) = '2004'
and datepart(mm,dateorderplaced) = '09'
and datepart(dd,dateorderplaced) = '30';
and got some results as follows
-----------------------------------------------------------------------------------------------------
Sep 30 2004 12:00AM
Sep 30 2004 12:00AM
Sep 30 2004 12:00AM
Sep 30 2004 12:00AM
Sep 30 2004 09:44AM
Sep 30 2004 12:00AM
Sep 30 2004 10:24AM
Sep 30 2004 10:26AM
Sep 30 2004 10:29AM
-----------------------------------------------------------------------------------------------------
So obviously I am either messing up sending the java.sql.Date or there is an inherent problem writing EJB-QLs to access SQL-Server 'datetime' types.
Any help would be appreciated.
Unsuccessfully tried using java.sql.TimeStamp instead of java.sql.Date
Thanks,
Sharad
