CODEWITHSUNDEEP
javadatejdbc
Dheeraj
Dheeraj

Reputation: 11

Compare two dates fetched from a sql table in java

I want to compare two dates fetched from a SQL query to know which one is greater. Date format in the sql is 

2018-11-22 11:12:38.291647

I tried using 

java.util.Date sqlDate=new java.util.Date(resultset.getDate().getTime());

But it keeps on asking to change the datatype to int.

I also tried using

SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
java.util.Date d=df.format(/*date String*/);

But this also does not work. it keeps on asking to change the datatype of d to String.

Upvotes: 1

Views: 1625

Answers (4)

Basil Bourque
Basil Bourque

Reputation: 338785

No “format” in database

Date format in the sql is 2018-11-22 11:12:38.291647

No, it is not.

Date-time values stored in date-time types have no “format”. Formats are for strings. A database stores a date-time value by its own internally-defined data structure. The details of that data structure is none of our business.

Smart objects, not dumb strings

Exchange date-time objects of a date-time class with a database for values stored in a date-time column.

As of JDBC 4.2, we can exchange java.time objects with the database. No need to ever again use those terrible legacy classes such as java.sql.Timestamp, java.sql.Date, java.util.Date, and java.util.Calendar.

For a moment, in the database use a column of a type akin to the SQL-standard TIMESTAMP WITH TIME ZONE.

OffsetDateTime

For such a column, pass a OffsetDateTime object to your prepared statement. Usually best to work in UTC.

Instant instant = Instant.parse( "2018-11-22T11:12:38.291647Z" ) ;  // `Z` on the end means UTC. Pronounced “Zulu”.
OffsetDateTime odt = instant.atOffset( ZoneOffset.UTC ) ;
myPreparedStatement.setObject( … , odt ) ;

Retrieval.

OffsetDateTime odt = myResultSet.getObject( … , OffsetDateTime.class ) ;

Compare

Compare OffsetDateTime objects using isBefore, isAfter, and isEqual.

boolean aIsBeforeThanB = odtA.isBefore( odtB );

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Upvotes: 0

forpas
forpas

Reputation: 164099

This type of format is comparable in java by comparing the strings, so you don't need any conversion:

String date1 = "2018-11-22 11:12:38.291647";
String date2 = "2018-11-23 10:11:00.090600";
int result = date1.compareTo(date2);

System.out.println(result);

will print

-1

because date2 is "greater" than date1.
if date1 is "greater" than date2 the result is 1 and
if date1 is "equal" to date2 the result is 0

Upvotes: 0

Lorelorelore
Lorelorelore

Reputation: 3393

First, convert in Date objects:

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.sss");
Date date = format.parse(yourDate);

and then compare them using Date.after or Date.before

Upvotes: 0

Gtomika
Gtomika

Reputation: 885

You can order the result of a query in SQL with the ORDER BY command. If you want the "biggest" date, you should order the query descending: ORDER BY DESC.

This way you can get the biggest date from the ResultSet object holding the result of your query by simply extracting the first element.

Upvotes: 2

Related Questions