aspose file tools*
The moose likes JDBC and the fly likes difference between two dates with excel as database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "difference between two dates with excel as database" Watch "difference between two dates with excel as database" New topic
Author

difference between two dates with excel as database

Jyotee agarrwal
Greenhorn

Joined: Sep 02, 2008
Posts: 10
I need to find difference between two dates.
I am using Java and Excel as database


static final String DRIVER_NAME = "sun.jdbc.odbc.JdbcOdbcDriver";
static final String DATABASE_URL = "jdbcdbcailyReport";



sb.append("SELECT trunc(to_date('" + dateFormat.format(date) + "', 'yyyy-mm-dd') - to_date('" + date1 + "', 'yyyy-mm-dd')) days FROM [Sheet1$]" );

My problem is on running the code functions like to_date and trunc are not recognised.

besides when i tried running

sb.append("SELECT " + dateFormat.format(date) + " - " + date1 + " FROM [Sheet1$]" );

it is not showing correct answer

Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
I am using Java and Excel as database

First of all, it is a really big misunderstanding that Excel is a database. It is not! It is a spreadsheet.
Examples of databases are Access, MySQL, MSSQL, PostgreSQL, Oracle, DB2, JavaDB (Derby) and Hypersonic.

In fact, misusing Excel as a database is just receipt for trouble. Handle it as a spreadsheet. There are Java API´s out for this, such as Apache POI HSSF and Andy Khan JExcelAPI. Information, tutorials and examples are available at their sites.

If you actually want a database, you shouldn´t be using Excel at all. Use a real database.
Jyotee agarrwal
Greenhorn

Joined: Sep 02, 2008
Posts: 10
Hi

Thank for the information .. If I would have been given the choice my work would be more easy.
The requirement is that only .. please tell me if anything can be done
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
Load the complete sheet into Java memory and use Java instead to do the work. It´s only very inefficient and memory consuming.

trunc and to_date are Oracle specific SQL functions. This is indeed not going to work in Excel (let alone another databases). I also highly doubt if the lousy ODBC bridge driver understands a little bit more than the standard SQL92 statements/functions.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: difference between two dates with excel as database