Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

difference between two dates with excel as database

 
Jyotee agarrwal
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2458
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic