CODEWITHSUNDEEP
javaoracle-databasejdbc
maas
maas

Reputation: 401

Update/ Retrieve /Inserting date field

I am having difficulties while updating a date field into the Database. The field type in the DB is Date/Time.

Now, I am trying to update the field name "R_Date".

Currently, I am using the SQL Expression in my jsp" UPDATE request SET request_date ='"+Request_Date+"'"; , But it is not accepting.

In the select statement I am using a normal select, I tried to use to_char or to_date, but it is not accepting the format of "DD-MMM-YYYY"

So, can you please help me to retrive/Update/Insert date field in the format of "DD-MMM-YYYY" the date field?

Upvotes: 0

Views: 16497

Answers (3)

BalusC
BalusC

Reputation: 1109570

The normal practice to store a timestamp in the DB (thus, java.util.Date in Java side and java.sql.Timestamp in JDBC side) is to use PreparedStatement#setTimestamp().

Date requestDate = getItSomehow();
Timestamp timestamp = new Timestamp(requestDate.getTime());
preparedStatement = connection.prepareStatement("UPDATE request SET request_date = ?");
preparedStatement.setTimestamp(1, timestamp);

The normal practice to obtain a timestamp from the DB is to use ResultSet#getTimestamp().

Timestamp timestamp = resultSet.getTimestamp("request_date");
Date requestDate = timestamp; // You can just upcast.

To convert between java.util.Date and java.lang.String you normally use SimpleDateFormat:

// Convert from String to Date.
String requestDateAsString = "09-Aug-2010";
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
Date requestDate = sdf.parse(requestDateAsString);

// Convert from Date to String.
String anotherDateAsString = sdf.format(someDate);

See also:

Upvotes: 2

YoK
YoK

Reputation: 14505

You need to check what date format you are trying to insert, and try using to_date method with appropriate format.

Following is referenced from : http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html

Oracle's default format for DATE is "DD-MON-YY". If you want to retrieve date in particular format you need to use :

    TO_CHAR(<date>, '<format>')

Similarly if you need to insert/update date with input of date other than in standard format, you need to use :

TO_DATE(<string>, '<format>')

where the <format> string can be formed from over 40 options. Some of the more popular ones include: 

    MM  Numeric month (e.g., 07)
    MON Abbreviated month name (e.g., JUL)
    MONTH   Full month name (e.g., JULY)
    DD  Day of month (e.g., 24)
    DY  Abbreviated name of day (e.g., FRI)
    YYYY    4-digit year (e.g., 1998)
    YY  Last 2 digits of the year (e.g., 98)
    RR  Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906
    AM (or PM)  Meridian indicator
    HH  Hour of day (1-12)
    HH24    Hour of day (0-23)
    MI  Minute (0-59)
    SS  Second (0-59)

Upvotes: 0

AHungerArtist
AHungerArtist

Reputation: 9599

I think you should use MON instead of MMM.

Have you tried something like:

UPDATE request
SET request_date = to_date('" + Request_Date + "', 'DD-MON-YYYY')

Hope you realize that as your statement stands (if it worked), it would update every row in the request table (not sure if that's your intention or not but I thought I'd point it out).

Upvotes: 1

Related Questions