jdbc date minus nowDate
I am trying to rest todays date minus a date coming from mySQL data base:
// I get no.sentDate in the format YYYY - MM - DD
conWorkPartyID = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
String sWorkOrderID = "select co.orderPartyid, no.workOrderListPartyid, wl.workOrdListDesc, no.sentDate from contractorOrders as co "+
"left join newOrders as no on no.orderPartyid = co.orderPartyid "+
"left join workOrderList as wl on wl.workOrderListPartyID = no.workOrderListPartyid "+
"where co.flagSentToContractor = 0 and co.contractorPartyid =" + sCompany;
cWorkPartyID = conWorkPartyID.executeQuery(sWorkOrderID);
while(cWorkPartyID.next()){
//this is the date that i need, which comes from db
Calendar cal = Calendar.getInstance();
Date dateTest = cWorkPartyID.getDate("no.sentDate", cal);
//get today's date
Date Now = new Date();
//rest today's date minus the date coming from db
cal.setTime(new Date(Now.getTime() - dateTest.getTime()));
//difference = cal.getTime();
//stores the years difference, which probably won't be needed
int year = cal.get(Calendar.YEAR);
//the varible months stores the months which probably won't be needed
int month = cal.get(Calendar.MONTH);
//the varible day stores who many days difference are there
int day = cal.get(Calendar.DAY_OF_MONTH);
System.out.println("and the difference between the insertDate and the today is in days: "
+ String.valueOf(day));
}
and the error is a java.lang.NullPointerException, any suggestions to change the above will be very much appreciated.
Thank you
Thanks for the reply, I found the problem, i was returning no.sentDate in the form YYYY-MM-DD and method getTime doesn't like so it was returning null, so what I have done was cast(no.sentDate as datetime) and also store the difference of the dates as a long variable as I need the difference between the dates not the day of the month.
My code now looks like this:
conWorkPartyID = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
String sWorkOrderID = "select co.orderPartyid, no.workOrderListPartyid, wl.workOrdListDesc, cast(no.sentDate as datetime) as newDate from contractorOrders as co "+
"left join newOrders as no on no.orderPartyid = co.orderPartyid "+
"left join workOrderList as wl on wl.workOrderListPartyID = no.workOrderListPartyid "+
"where co.flagSentToContractor = 0 and co.contractorPartyid =" + sCompany;
cWorkPartyID = conWorkPartyID.executeQuery(sWorkOrderID);
while(cWorkPartyID.next()){
Object x = cWorkPartyID.getObject("co.orderPartyid");
Object y = cWorkPartyID.getObject("no.workOrderListPartyid");
Object z = cWorkPartyID.getObject("wl.workOrdListDesc");
Date dateTest = cWorkPartyID.getDate("newDate");
Date Now = new Date();
System.out.println("dateTest is: " + dateTest.getTime());
long dif = ((Now.getTime() - dateTest.getTime())/86400000);// milliseconds to days
System.out.println("Order PartyID that require your attention is : "+ x + " and workOrderPartyid: " + y +" and WorkOrderListDesc: "
+ z + " and the sent date is: "+ dateTest + "and the difference between the insertDate and the today is in days: "
+ dif);
}
cWorkPartyID.close();
Any other suggestions are welcome.
Thanks for answering so soon.
Good night!
