• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

finding transactions which occurred yesterday

 
Rion Morgenstern
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am having some trouble with DAY addition and subtraction. I would like to find all transactions that occurred yesterday. my TRANSACTION_DATE field hold a date value like '2012-06-01'

Here is my current query. No errors and the data returned is still the current date only.



I am more comfortable with MSSQL and this is a JDBC compliant database so I am a little lost.

Thanks for the help.
 
Greg Charles
Sheriff
Posts: 3002
12
Firefox Browser IntelliJ IDE Java Mac Ruby
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rion, welcome to JavaRanch!

I'd probably use a prepared statement. Something like:




(Note: the ellipsis (...) is where you put the rest of your select statement.)

So, that just leaves the problem of how to get one day ago in a Date object (yesterday in my example). That question is answered over at http://www.coderanch.com/t/378763/java/java/Yesterday-date
 
Rion Morgenstern
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the quick response but I am a little confused by your reply. I am programming this directly into a query window. So the "connection" object does not exist. In addition what does the "?" in the "WHERE INHI.TRANSACTIONDATE >= ?" mean?

Is there a native query method to get this done? In transactSQL it would look something like this:



Thanks!
 
Paul Clapham
Sheriff
Posts: 21550
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rion Morgenstern wrote:Is there a native query method to get this done?


I might have missed it, but I don't see where you said which database you were using. I guess it isn't MS SQL because you said you said you were more comfortable with that, but I don't have a guess about what it is. And your question does seem to be about a particular database.
 
Rion Morgenstern
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The database is JDBC compliant PRO/5 BBJ database. Not very common. It powers our manufacturing database from OSAS (Open Systems Accounting Software)
 
William P O'Sullivan
Ranch Hand
Posts: 859
Chrome IBM DB2 Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Haven't heard of that one.

However it will depend on if there is a DB function that can subtract "days".
It may also depend on the internal mechanism for storing dates.

Some DBs store as numbers that reflect the amount of miliseconds since a starting point (Oracle I think).

You probably have to research this BBJ DB and let the DB deal with the subtraction.

If that fails, you can always get todays date and twek it using Calendar() functions the select using that.

WP
 
Rion Morgenstern
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Found a solution. It's very KISS (Keep It Simple Stupid)



CURRENT_DATE is a SQL92 standard that can have days added or subtracted very easily.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic