Results 1 to 8 of 8
  1. #1
    computerbum is offline Member
    Join Date
    Feb 2010
    Posts
    26
    Rep Power
    0

    Default convert String date to Date

    Hello,

    I am trying to convert this string formatted date "2010-09-06"

    to '06-SEP-10'

    so the oracle database can query from it.

    Could someone tell me how?

    I used this but it is not working:

    // where approvedDateFrom is passed in as "2010-09-06"

    public void setApprovedDateFrom(String approvedDateFrom) throws ParseException {

    SimpleDateFormat df = new SimpleDateFormat("DD-MMM-yy");


    Date apprDate = df.parse(approvedDateFrom);
    this.approvedDateFrom = apprDate;
    }

  2. #2
    JosAH's Avatar
    JosAH is offline Moderator
    Join Date
    Sep 2008
    Location
    Voorschoten, the Netherlands
    Posts
    13,651
    Blog Entries
    7
    Rep Power
    21

    Default

    Quote Originally Posted by computerbum View Post
    // where approvedDateFrom is passed in as "2010-09-06"
    SimpleDateFormat df = new SimpleDateFormat("DD-MMM-yy");
    Check your format string; DD-MMM-yy doesn't match a date in the format 2010-09-06.

    kind regards,

    Jos

  3. #3
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,089
    Rep Power
    20

    Default

    Dates do not have a format.
    DATE columns in a db do not have a format.

    Use the setDate() of PreparedStatement to set the DATE values in your query.
    This takes a java.sql.Date object, which you can create from a java.util.Date by doing "new java.sql.Date(utilDate.getTime())".

    So, convert from the String to a util.Date as you are at the moment, then turn that into a sql.Date...

    ETA: And I missed the formats didn't match...:)

  4. #4
    computerbum is offline Member
    Join Date
    Feb 2010
    Posts
    26
    Rep Power
    0

    Default

    I set the formmatting like so but it returns: Fri Jan 01 00:00:00 EST 2010

    public void setApprovedDateFrom(String approvedDateFrom) throws ParseException {

    SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");


    Date apprDate = df.parse(approvedDateFrom);
    this.approvedDateFrom = apprDate;
    }

  5. #5
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    Can you alter your SQL and use a setString instead of a setDate?

    For example:
    In your SQL you may have a date called update_date

    Java Code:
    SELECT col_name from table_name
    WHERE update_date = TO_DATE(?, 'YYYY-MM-DD');

  6. #6
    computerbum is offline Member
    Join Date
    Feb 2010
    Posts
    26
    Rep Power
    0

    Default

    Ok, here is the issue I am running into:

    When I pass my information to the database which is formatted like so:

    SELECT t1.* FROM csc_rdmp t1 WHERE t1.rel_appr_date = '2010-09-01' AND t1.rel_appr_date <= '2010-09-17'

    I get the error:

    An error was encountered performing the requested operation

    ORA-01861 literal does not match format string.


    I know my problem is because it does not know how to handle the dates.

    I need a way to handle the dates so I can perform queries base on the user supplied dates.

  7. #7
    SFNYC is offline Member
    Join Date
    Sep 2010
    Posts
    16
    Rep Power
    0

    Default

    If rel_appr_date is an Oracle DATE column you should convert your Java Date variable to a String and do a setString for it. In Oracle you should always convert your variables to DATE columns, never let Oracle do the conversion for you because you are not always going to be 100% sure what the default DATE format is.

    So,
    SQL
    Java Code:
    SELECT t1.* FROM csc_rdmp t1 WHERE t1.rel_appr_date = [B]TO_DATE(?[/B], 'YYYY-MM-DD') AND t1.rel_appr_date <= [B]TO_DATE(?,[/B] 'YYYY-MM-DD')
    Java
    Assuming
    apprDate is a Java Date variable.

    Java Code:
    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    String approvedDateFromAsString = formatter.format(apprDate);
    setString(1, approvedDateFromAsString);
    setString(2, approvedDateFromAsString);

  8. #8
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,089
    Rep Power
    20

    Default

    Quote Originally Posted by SFNYC View Post
    If rel_appr_date is an Oracle DATE column you should convert your Java Date variable to a String and do a setString for it. In Oracle you should always convert your variables to DATE columns, never let Oracle do the conversion for you because you are not always going to be 100% sure what the default DATE format is.

    So,
    SQL
    Java Code:
    SELECT t1.* FROM csc_rdmp t1 WHERE t1.rel_appr_date = [B]TO_DATE(?[/B], 'YYYY-MM-DD') AND t1.rel_appr_date <= [B]TO_DATE(?,[/B] 'YYYY-MM-DD')
    Java
    Assuming
    apprDate is a Java Date variable.

    Java Code:
    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    String approvedDateFromAsString = formatter.format(apprDate);
    setString(1, approvedDateFromAsString);
    setString(2, approvedDateFromAsString);
    No.
    No.
    No.
    And no.

    You use a Date.

    Do not convert your Date to a String, that is out and out wrong.

    You use a PreparedStatement and the setDate() method. The ides of having a Date and then converting it to a String and then having the database convert it to a DATE again is just plain wrong.

    To the OP, how are you creating your SQL? I hope you are using a PreparedStatement.
    Please show use the whole code for the statement to execution.

Similar Threads

  1. how we can convert string to date?
    By cowboy2010 in forum New To Java
    Replies: 4
    Last Post: 08-25-2010, 08:41 PM
  2. Convert date from database
    By karq in forum New To Java
    Replies: 5
    Last Post: 07-13-2010, 08:44 AM
  3. Compare date input to database with current date
    By hungleon88 in forum Advanced Java
    Replies: 2
    Last Post: 11-25-2008, 08:10 AM
  4. how to convert date format
    By saran123 in forum New To Java
    Replies: 5
    Last Post: 10-16-2008, 06:10 PM
  5. Creating a Gregorian Calendar using a Date object gives date - 1
    By prachi_goliwadekar in forum New To Java
    Replies: 1
    Last Post: 05-08-2008, 08:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •