Java: SQL date format

Question

Is there any to assign SQL date directly instead of converting through SimpleDateFormat and parse to sql date. I am reading few date fields from a file lets say 03/09/2017 and I also have its oracle equivalent format defined as a field definition mm/dd/yyyy.

I am reading the date and its format through Java and inserting to Database. Currently I am assigning the date format to SimpleDateFormat and parsing the date read from file and converting to SQL date as follows.

SimpleDateFormat YMDFmt = new SimpleDateFormat("mm/dd/yyyy"); 
// This is not hardcoded field. 
// Its field definition will be directly assigned as defined. In this case it is mm/dd/yyyy
Date date;
String datefield="03/09/2017";
date=YMDFmt.parse(datefield);
java.sql.Date sqldt= new java.sql.Date(date.getTime());

Here my problem is due to varying formats, mm is treated as minutes in java where as it is month in oracle. I will have to change to 'MM' to make it work in java but in my scenario i dont know the original format of date. i will have to read the format and parse accordingly? Please advise if there is anyway to just convert oracle format to equivalent java format (mm/dd/yyyy to MM/dd/yyyy and so on) or any other solution. Thank you.

Of course you'll have to know the format of the date that you're reading from a file. As far as database is concerned, there's no date format there. Databases store date as Unix timestamp, that's a number (Number of milliseconds from Epoch, in GMT). So when reading from the database you're not reading a formatted string, but a Date object (timestamp), there's nothing to format there. — 11thdimension
– 11thdimension, Commented Apr 3, 2017 at 14:28
The format in Oracle has nothing to do with the format you use to parse. You should set your date format according to the format in the file. If you don't know what that is, you can't expect to get the correct values from that file. — RealSkeptic
– RealSkeptic, Commented Apr 3, 2017 at 14:29
A DATE column in the the Oracle database has no "format". You should pass the date value as an instance of java.sql.Date through a PreparedStatement. There is no need to deal with any date formats when dealing with values from the database. — user330315
– user330315, Commented Apr 3, 2017 at 14:40
I don’t get the part about the varying formats. Are you saying that the file may contain either, say, 03/31/2017 or 31/03/2017 for 31 of March? If so, how do you determine which? I also don’t understand why you’re concerned with Oracle’s format. — Anonymous
– Anonymous, Commented Apr 3, 2017 at 14:46
As an aside, if you can use Java 8, you should probably prefer to use LocalDate instead of Date, it’s generally much nicer to work with. If you have a sufficiently new JDBC driver, you should be able to hand it a LocalDate instance directly (so I have heard). — Anonymous
– Anonymous, Commented Apr 3, 2017 at 14:49

RealSkeptic · Accepted Answer · 2017-04-03 15:52:33Z

If your database is Oracle, you can change your prepared statement so that it includes a TO_DATE call instead of the date itself.

So, if your current insert statement is something like

String sql = "INSERT INTO foo (datefield) VALUES (?)";
PreparedStatement stmt = con.prepareStatement(sql);

And you use something like stmt.setDate(1,sqldt) to fill in the value, then you can change it into something like

String sql ="INSERT INTO foo (datefield) VALUES (TO_DATE(?,?))";
PreparedStatement stmt = con.prepareStatement(sql);

And use

String oracleFormat = "mm/dd/yyyy"; // Not hard-coded in real life
String dateStr = "03/09/2017";      // Not hard-coded in real life
stmt.setString(1,dateStr);
stmt.setString(2,oracleFormat);
stmt.executeUpdate();

In this case, Oracle will convert the string into a date for you, instead of having Java do that.

Pay attention to changing all the other parameter numbers to fit.

Collectives™ on Stack Overflow

Java: SQL date format

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related