Convert date format to 'dd-MMM-yyyy'

Question

So I have a function which gets a java.sql.Date fromDate as a input parameter. Its in the format yyyy-MM-dd(2014-05-01). Now i want to convert it to format dd-MMM-yyyy(01-May-2014) to insert into oracle db. I must use the setDate function in the PreparedStatement object. Any help is appreciated. Is there a way to convert a sql Date to a different format without converting it to a String?

public class Sample{

private static final String CHECK_HOLIDAY = " select count(*) as noofdays from TLTA.SITE_HOLIDAY where (HOLIDAY_DATE between ? and ? )";

public int getSiteHolidays(Leave leave) {
    int holidays = 0;
    try {
        Connection con = ConnectionProvider.getCon();
        PreparedStatement ps = con.prepareStatement(CHECK_HOLIDAY);
        ps.setString(1,leave.getFromDate());
        ps.setString(2,leave.getToDate());
                  /*
                   *other code
                   */
                }
            catch(Exception e){
            }
          }
      }

The Date that is got from leave.getFromDate() is 2014-05-01. When this is bound to the PreparedStatement I am getting an error during the execution of the query which is

ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal.

The dates stored in the database are in the format 01-May-2014. Also when I try executing the query by giving date in 01-May-2014 format it executes. Also I tried the other fixes suggested on StackOverflow. Did not work for me. How can I fix this?

Also, a `PreparedStatement#setDate()` does not expect a format, it expects a date. — Sotirios Delimanolis, Jun 13 '14 at 05:14
Use `setDate` and pass it the `fromDate` value, let the database take care of the rest. — MadProgrammer, Jun 13 '14 at 05:15
So I use `setString()` by converting the date to a String in the format I want? — Conquest, Jun 13 '14 at 05:16
No, you need to understand that a date **does not have a format**. It is an instant in time. The format is just something the database shows you, in human readable form. — Sotirios Delimanolis, Jun 13 '14 at 05:19
But the query fails when I pass the Date as '2014-05-01'. ORA-01861: literal does not match format string 01861. 00000 - "literal does not match format string" *Cause: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the "FX" modifier has been toggled on, the literal must match exactly, with no extra whitespace. *Action: Correct the format string to match the literal. — Conquest, Jun 13 '14 at 05:21
Why don't you show us what you are doing? We will consider re-opening the question. — Sotirios Delimanolis, Jun 13 '14 at 05:22
But before you do, look up the error on stackoverflow. There are so many related posts. — Sotirios Delimanolis, Jun 13 '14 at 05:25

Convert date format to 'dd-MMM-yyyy'

0 Answers0