jQuery in Action, 3rd edition
The moose likes Beginning Java and the fly likes Converting Java Dates and Database Dates Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Java » Beginning Java
Bookmark "Converting Java Dates and Database Dates" Watch "Converting Java Dates and Database Dates" New topic

Converting Java Dates and Database Dates

Greg Mathaus

Joined: Jun 29, 2001
Posts: 4
Hi All,
I have a problem on which I am breaking my head.
I have a reports table in the database( May be Oracle/ SQL or any DB) that has a date field and other columns.
Say it looks like

This table is updated every 24 hours ( which means only one insert to this table every day). I am looking for a method that takes all the records in this table that exist between a start date and the next 'n' days, and also provide my front-end JSPs a Vector of all dates that exist in this list as a string array. The date format I prefer is 'DD-MON-YY'.
I am struggiling with the addition of 'n' days to the start date and forming my database query.
Some of my sample reports on this table would be.
Number of Visitors to the site from '20-JUN-01' on a day by day basis stopping after 15 days
Number of Visitors to the site on a week by week basis. etc.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
You can pass the information to oracle, and oracle can do the calcs.
select sysdate + 16 from dual
will give you the date plus 16.
Remember that oracle is working with the date and time when using a date field. So store your dates as midnight or you will need to truncate it out.
Greg Mathaus

Joined: Jun 29, 2001
Posts: 4
I need this to be generic across Databases.
Mike Curwen
Ranch Hand

Joined: Feb 20, 2001
Posts: 3695

Investigate the use of the Calendar class. I needed the same sort of thing... for weeding out old records. I wanted to delete records older than 30 days.

So i took todays date, and I used the Calendar class to add 30 days (well, the GregorianCalendar Class actually).

So it should be simple to build a SQL query with two different dates. Something like "SELECT * FROM tablename WHERE datefield BETWEEN " + startDate + " AND " + endDate
where startDate and endDate are java.sql.Date() objects.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
subject: Converting Java Dates and Database Dates
It's not a secret anymore!