Update/ Retrieve /Inserting date field

Question

Hello All,

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?

Answer 1

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)

Answer 2

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:

• PreparedStatement tutorial
• How to avoid Java code in JSP file (!!!)