Date and Timesone issues in finder methods

[lduperval]

Hi,

I am using JBoss 3.2.5 on Windows XP Pro with MS SQL Server 2000.

We have configured our database so that all times are UTC. In my user interface, I allow users to search for data using three types of time zones, one of which is GMT.

I created a finder method that receives a java.util.Date object as input. That date object can have any type of time zone. However, when I do a trace of the SQL query that is sent to the server, it always sends a time string local to my machine.

For example, if I send a request to find data from Feb 6 00:00 GMT to Feb 7 00:00 GMT, the Date is translated to Feb 5 19:00 EST to Feb 6 19:00 EST. The actual string sent to SQL server is:

'Feb 5 2006 7:00:00:000PM', 'Feb 6 2006 7:00:00:000PM'

This is wrong and gives me incorrect results.

Does anyone know how to make the date translate to

'Feb 6 2006 0:00:00:000AM', 'Feb 7 2006 0:00:00:000AM'

in my SQL query?

Thanks,

L

[lduperval]

Ok, after messing around with my code to do offset calculations and things of that nature, I realise that what I really need is a way to tell JBoss that all times in my database tables are UTC() dates. When data is fetched and retrieved, somehow my application seems to think that everything is in local time.

I am not sure where I need to change configuration settings to state that all times in the databse are UTC. If anyone can help, I would really appreciate it.

Thanks,

L