aspose file tools*
The moose likes JDBC and the fly likes Comparing Dates in jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Comparing Dates in jdbc" Watch "Comparing Dates in jdbc" New topic
Author

Comparing Dates in jdbc

ohad perets
Greenhorn

Joined: Oct 30, 2011
Posts: 1
hi,
i have a problem i cant compare dates and times in java...
im saving the date in Date format in the phpmyAdmin in Sql, also the Time
and i cant compare dates with this code:

if(checkDate && checkTime)//both
sqlStm = "SELECT * FROM "+tableName+" WHERE startDate >= "+startDate2+" AND exitDate <= "+exitDate2+" AND "+"startTime >= "+startTime2.getTime()+" AND exitTime <= "+exitTime2.getTime();
else if(checkDate && !checkTime) //By date
sqlStm = "SELECT * FROM "+tableName+" WHERE startDate >= "+startDate2+" AND exitDate <= "+exitDate2;
else if (checkTime && !checkDate) //By time
sqlStm = "SELECT * FROM "+tableName+" WHERE startTime >= "+startTime2.getTime()+" AND exitTime <= "+exitTime2.getTime();
else //show all
sqlStm = "SELECT * FROM cardata ORDER BY `totalPayment` DESC";
any solutions guys???
please help me


this is my insert:

PreparedStatement statement = connection
.prepareStatement("INSERT INTO `ParkingLot`.`cardata` (`startDate`,`startTime`,`exitDate`,`exitTime`,`carNumber`,`totalPayment`) VALUES (?,?,?, ?,?,?) ");
statement.setDate(1, new Date(startDate.getYear(), startDate.getMonth(), startDate.getDay()+16));
statement.setTime(2, new Time(startTime.getHours(), startTime.getMinutes(), startTime.getSeconds()));
statement.setDate(3, new Date(exitDate.getYear(), exitDate.getMonth(), exitDate.getDay()+16));
statement.setTime(4, new Time(exitTime.getHours(), exitTime.getMinutes(), exitTime.getSeconds()));
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

You didn't describe the "problem" you're experiencing, so I'll just make a few general notes:

1) You need to bind the values in all of your SQL queries. You're doing so in the insert statement, so you already know the way. Your current code relies on implicit conversions, which depend on locale settings (and I bet this is the cause of the unspecified "problem" you're having). Furthermore it is subject to SQL injection attacks.

2) The checkDate && checkTime branch has a problem of its own: assume the start date/time is yesterday at 14:00, exit date/time is today at 10:00. Do you see your condition cannot be ever satisfied? What you probably need to do is to store the date and time in the same field. (Of course, I might have understood your code wrong, you specified neither the meaning of your fields, nor the intentions of your code.)
abani patra
Ranch Hand

Joined: Oct 11, 2011
Posts: 70
Hi,
I want to see if you are getting any exception , what is that exception.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Comparing Dates in jdbc