Java Calendar/Date to SQL Timestamp / Oracle Database

Question

Currently i am trying to insert a Timestamp value into a Oracle Database Timestamp Field.

Using CURRENT_TIMESTAMP i can insert data to the database

The timestamp it returns is (2013-11-20.14.50.7.832000000)

So il explain the issue. I need a created date/time (timestamp) and a expired date/time (timestamp). so i am using Java calendar to process the extra days. for example adding 365 days to get a year expiration.

Here is my current Java Date code:

    public Date GetCurrentDate(HttpServletRequest request, HttpServletResponse response) throws Exception{

    //Create current Date
    Calendar cal = new GregorianCalendar();
    Date creationDate = cal.getTime();

    
    
    return creationDate;


}

     Date datereturn = GetCurrentDate(request,response);
         java.sql.Timestamp timestampcurrent = new Timestamp(datereturn.getTime()); 
         timestampcurrent.setNanos(0);

Below is the code adding 2 hours to the current date to make the expiration date and adding it to a timestamp.

//Set Expired Date/Time Based from xml Evaluation (Days)
            Calendar cal = Calendar.getInstance();
            cal.setTime(datereturn);
            cal.add(Calendar.DAY_OF_YEAR,Integer.parseInt(getServletContext().getInitParameter("EXPIRED_DAYS_EVALUATION"))); // this will add two hours
            expireddatereturn = cal.getTime();
            timestampexpired = new Timestamp(expireddatereturn.getTime());
            timestampexpired.setNanos(0);
            logText.info(timestampexpired   + "    " + timestampcurrent .toString());

so i now have two timestamps, "timestampcurrent" (current date) and "timestampexpired" (expiration date).

i am trying to insert these values into a oracle database but i recieve a error:

                String sqlInsertData ="INSERT INTO EC_TABLE" +
                    "(licenseid, customername, description, servername,licensetype, username,password, createdDateTime,ExpiredDateTime)" +
                    " VALUES ('"+LicenseID+"','"+CustomerName+"','"+Description+"','"+ServerName+"','"+LicenseType+"','"+EncryptedUsername+"','"+EncryptedPassword+"','"+timestampcurrent+"','"+timestampexpired+"')";

THE ERROR IS : ORA-01843: not a valid month

Been trying to fix this for hours but i cannot find the issue!. please help!.

additional information:

logText Returns:

logText.info(timestampcurrent + " \ " + timestampcurrent.toString());

INFO [http-8080-2] (ecsystem.java:233) - 2013-11-20 15:34:55.0 \ 2013-11-20 15:34:55.0

logText.info(timestampexpired + " \ " + timestampexpired.toString());

INFO [http-8080-2] (ecsystem.java:233) - 2013-11-22 15:34:55.0 \ 2013-11-22 15:34:55.0

Hope all this information helps!

Have a look at this [post][1]. That should answer your question. [1]: stackoverflow.com/questions/5265000/… — benjamin.d
– benjamin.d, Commented Nov 20, 2013 at 16:04
I suppose it should be cal.add(Calendar.HOUR, .... And never assemble SQL this way - use a PreparedStatement and let it do the formatting work. — Gyro Gearless
– Gyro Gearless, Commented Nov 20, 2013 at 16:10

JB Nizet · Accepted Answer · 2013-11-20 16:11:44Z

2

You should never use String concatenation to add dynamic parameters to a query, for the follwoing reasons:

the formats used for dates, times, etc. vary from database to database, end even locale to locale. And the toString() representation of the Java types don't necessarily match with those expected by the database
as soon as you have a single quote or a line break in a string, the query will become invalid
some types (like byte arrays, etc.) don't have any string representation
this opens your application to SQL injection attacks.

You should thus use prepared statements:

String sql =
    "INSERT INTO EC_TABLE" +
    "(licenseid, customername, description, servername,licensetype, username,password, createdDateTime,ExpiredDateTime)" +
    " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setString(1, licenseId);
...
stmt.setTimestamp(9, timestampexpired);

answered Nov 20, 2013 at 16:11

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Understanding-Tech Over a year ago

so i just put a question mark in the values and use the stmt below or swap the ? for 1 2 3 4 5 etc?

JB Nizet Over a year ago

Yes. You might also click the link I posted to the official Java tutorial and take 10 minutes to read and fully understand the concept and usage of prepared statements.

Understanding-Tech Over a year ago

freaking genious!, worked perfectly and i had a read about the security issues you mention and i shall go through the link to get a better understanding of it all. Thank you so much for the information.

sumitsabhnani · Accepted Answer · 2013-11-20 16:10:13Z

0

You can use following while inserting

to_date('"+timestampcurrent+"', 'YYYY-DD-MM HH:MI:SS')

and similarly for timestampexpired, this should fix this problem.

answered Nov 20, 2013 at 16:10

sumitsabhnani

3101 silver badge6 bronze badges

1 Comment

Understanding-Tech Over a year ago

" ORA-00947: not enough values" error and the info statement returns to_date('2013-11-20 16:14:55.0', 'YYYY-DD-MM HH:MI:SS'),to_date('2013-11-22 16:14:55.0', 'YYYY-DD-MM HH:MI:SS') is it still meant to have to the yyyy-dd-mm their ?

Collectives™ on Stack Overflow

Java Calendar/Date to SQL Timestamp / Oracle Database

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related