I have date data in the db in the following format:

 07-FEB-18 09.43.51.712992 AM

But the incoming input format is :

2018-02-07T09:43:51.712992

So I converted my input to the expected format to be able to check against db value. Here is the some piece of my code snippet.

 public class Main{
        public static void main(String[] args){
            DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yy HH.mm.ss.SSSSSS aa",Locale.ENGLISH);  
            strDate = "2018-02-07T09:43:51.712992"
            String year = strDate.substring(2,4);
            String month = CommonUtil.getMonthValueWithLetters(strDate.substring(5,7));
            String day = strDate.substring(8,10);
            String hour = CommonUtil.convertToPMAM(strDate.substring(11,13)).keySet().iterator().next();
            String min = strDate.substring(14,16);
            String second = strDate.substring(17,19);
            String milliSecond = strDate.substring(20,26);
            String AMPM = CommonUtil.convertToPMAM(strDate.substring(11,13)).values().iterator().next();
            String convertedStrDate = day + "-" + month + "-" + year + " " + hour + "." + min + "." + second + "." + milliSecond + " " + AMPM; //07-FEB-18 09.43.51.712992 AM
            Date timeStamp = dateFormat.parse(convertedStrDate);  //Wed Feb 07 09:55:43 GMT+04:00 2018          
        } 
    }   

    public class Commonutil{
        public static String getMonthValueWithLetters(String month){
            switch(month){
                case "01": return "JAN";
                case "02": return "FEB";
                case "03": return "MAR";
                case "04": return "APR";
                case "05": return "MAY";
                case "06": return "JUN";
                case "07": return "JUL";
                case "08": return "AUG";
                case "09": return "SEP";
                case "10": return "OCT";
                case "11": return "NOV";
                case "12": return "DEC";
                default : return null;              
                }
        }
        public static   Map<String, String> convertToPMAM(String hour){
                Map<String, String> keyValues  = new HashMap<String, String>();
                int value = Integer.parseInt(hour);
                if( value <=12){
                    keyValues.put(hour, "AM");
                        return keyValues;
                    }else{
                        int temp = value - 12;
                        if(temp<10){
                            keyValues.put("0"+String.valueOf(value-12),"PM");
                            }
                        else{
                            keyValues.put(String.valueOf(value-12), "PM");
                            }
                        }        
                        return keyValues; 
                    }   
                }   

As it seems parse method converts microseconds as well so whenever I pass this value to the db , it can not be checked against the value in the db. So is there any way that I can convert my incoming date value to the required format shown in the db?

For your information I am using jdbc and oracle db.

upvote
  flag
Isn't date field in your database of type Date? If so, shouldn't you just convert the input into date and then compare? – Plirkee
upvote
  flag
@Plirkee Nope it is Oracle type TimeStamp – devgirl
upvote
  flag
I recommend you avoid the SimpleDateFormat class. It is not only long outdated, it is also notoriously troublesome. Today we have so much better in java.time, the modern Java date and time API. – Ole V.V.
1 upvote
  flag
You are greatly overcomplicating things. Your incoming format, 2018-02-07T09:43:51.712992, matches that required by the one-arg LocalDateTime.parse(String), so use this. With a just fairly new JDBC driver you should be able to store your LocalDateTime into the timestamp column of your Oracle database. No need for any conversion in your Java code. – Ole V.V.
upvote
  flag
It’s unclear to me, what exactly do you mean by “check against db value”? Are you using the incoming date-time in a query? Or comparing it to a timestamp retrieved from the database? Should it be the same, before, after…? – Ole V.V.

3 Answers 11

up vote 0 down vote accepted

java.util.Date only has an accuracy of milliseconds. If you need a higher accuracy when dealing with a database (down to one nanosecond), you will have to use the java.sql.Timestamp class instead. By replacing the 'T' separator between date and time in your input format, you can feed the string directly to a factory method and get the expected result:

Timetstamp t = Timestamp.valueOf("2018-02-07T09:43:51.712992".replace('T', ' '));

You can then use the timestamp directly in your JDBC statements.

1 upvote
  flag
Alternatively, one could use a LocalDateTime. – Mark Rotteveel

If you want to pass that string into Oracle, you can convert it to a timestamp:

select TO_TIMESTAMP('2018-02-07T09:43:51.712992', 'YYYY-MM-DD"T"HH:MI:SS.FF6') from dual;

Or, adding a timezone to the string:

select TO_TIMESTAMP_TZ('2018-02-07T09:43:51.712992 -5:00', 'YYYY-MM-DD"T"HH:MI:SS.FF6 TZH:TZM') from dual;

Put the "T" separator in double quotes might be the tricky part thats not obvious. The Oracle format docs are here. The timezone support docs are here.

As I understand your question, you are getting input in a format like 2018-02-07T09:43:51.712992, and you need to compare this input to timestamps in your Oracle database.

Your input string conforms with ISO 8601. The classes of java.time, the modern Java date and time API, parse ISO 8601 format as their default, that is, without any explicit formatter. This makes parsing your input easy:

    String inputString = "2018-02-07T09:43:51.712992";
    LocalDateTime inputDateTime = LocalDateTime.parse(inputString);

Using a JDBC 4.2 compliant JDBC driver (which you probably already have and can at least get for your Oracle database) you can pass this LocalDateTime directly to your query. I am assuming your query is represented as a PreparedStatement in Java. For example:

    yourPreparedStatement.setObject(1, inputDateTime);

Now you can compare the input value directly with timestamps from the database in your query. No need for your format conversion.

But as answer to your title: no, SimpleDateFormat cannot support microseconds. It only has a precision of milliseconds.

Link: Oracle tutorial: Date Time explaining how to use java.time.

upvote
  flag
thanks for the answer, it is a nice hack! But I have inherited code base , so i am not supposed to use java.time API – devgirl
upvote
  flag
A funny restriction, IMHO (not to say absurd). So you are not allowed to write the most readable and maintainable code? Use of java.time here can coexist nicely with whatever is already in your codebase (and might be a first step in a long lasting transition to the better future). If that were me, I’d prefer to challenge what I was supposed and not supposed to use. I’m not saying it’ll get you anywhere where you are, of course. – Ole V.V.

Not the answer you're looking for? Browse other questions tagged or ask your own question.