Format Code Appears Twice

Question

I am getting the following error on the oracle query below. I'm not seeing an issue with the format that I am giving in the query. I'm thinking it might have something to do with the .SSS but I can't be certain:

SELECT
     *   
 FROM
     (   
         SELECT
             *   
         FROM
             comprater_requests
         WHERE
             spname =?
             AND effectivedate >= TO_DATE(?,'yyyy-MM-dd HH:mi:ss.SSS')
             AND effectivedate <= TO_DATE(?,'yyyy-MM-dd HH:mi:ss.SSS')
     )   
 WHERE
     ROWNUM <= 100

Error:

ORA-01810: format code appears twice

Parameters:

Parameters: [Google, 2018-07-24 00:00:00.000, 2018-09-06 00:00:00.000]

Table:

    CREATE TABLE COMPRATER_REQUESTS
(
  ID                    NUMBER DEFAULT COMP_RATER_SEQ.NEXTVAL PRIMARY KEY,
  TRANSACTIONID         VARCHAR2(20 BYTE) NOT NULL,
  QUOTE                 CLOB,
  ARCHIVEXML            CLOB,
  ADDITIONALINFO        CLOB,
  QUOTEID               VARCHAR2(20 BYTE),
  AGENTID               CHAR(50 BYTE),
  EFFECTIVEDATE         DATE
);

The user selects two dates from the UI which is sent to the back end code in the following format 'Tue Sep 04 00:00:00 EDT 2018' but the oracle DB has the dates stored as '2018-09-04 00:00:00.0'. So I tried converting the date they select to that format by doing:

 dateFormat = new SimpleDateFormat(CompRaterPropertiesML.ACORD_DB_DATE_FORMAT)
 fromDateFormat = dateFormat.format(selectedDate1)
 toDateFormat = dateFormat.format(selectedDate2)

This converts it to the format I need but it is now a string which I believe is giving me the issue.

score 2 · Answer 1 · answered Aug 24 '18 at 14:13

TO_DATE converts to a date, not to a timestamp.

In Oracle, dates do not have fractions of a second. What you want to create are timestamps; for that, use TO_TIMESTAMP.

After you do, you will get the same error; to indicate fractions of a second, use .ff, not .sss. (NOTE: Two f's, not three!)

You are also missing 24 after hh. Should be '... hh24:mi:ss.ff'

score 1 · Answer 2 · answered Aug 24 '18 at 14:30

1

You are using parameters. Don't pass the values in as strings -- use the appropriate data type:

SELECT *   
FROM (SELECT cr.*   
      FROM comprater_requests cr
      WHERE spname = ? AND
            effectivedate >= ? AND
            effectivedate <= ?
     ) cr 
 WHERE ROWNUM <= 100 ;

answered Aug 24 '18 at 14:30

Gordon Linoff

1,242,037
58
646
786

Thanks. I've tried that and I get "Literal ford not match format string". The DB saves the effective date in the following format: 2018-07-24 00:00:00.0. – iceMan33 Aug 24 '18 at 15:16
@MjM8082 . . . What type are you using to pass the value in? – Gordon Linoff Aug 24 '18 at 15:39
I just updated my post which explains it a little more. Hopefully more clear what is happening. – iceMan33 Aug 24 '18 at 16:01

score 1 · Accepted Answer · answered Aug 27 '18 at 16:10

I actually figured out the issue.

The Date that was stored in the oracle DB was different from the format that was being passed into the query to perform the search. So I changed the format of the parameters to be a string in the format of 'YYYY-MM-DD' and then changed the format of the EFFECTIVEDATE with TO_CHAR. So my query looks like the following:

SELECT * FROM ( SELECT * FROM COMPRATER_REQUESTS WHERE SPNAME = ? AND TO_CHAR(EFFECTIVEDATE,'yyyy-mm-dd') >= ? AND TO_CHAR(EFFECTIVEDATE,'yyyy-mm-dd') <= ? ) WHERE ROWNUM <= 100

Format Code Appears Twice

3 Answers3