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 SITE_ID, NUM_VISITORS, DATE
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. TIA, Greg
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. Dan
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.