Date format returning ORA-00904 in java code but not in sql developer

Question

I am using spring JDBCTemplate to query my data.

I have the following query in my code

static final String SQL = SELECT msgs.msg_submit_date MSG_DATE FROM {0}.messages msgs 
WHERE TRUNC(msgs.msg_submit_date) 
BETWEEN TO_DATE(:startDate,'YYYY-MM-DD') AND TO_DATE(:endDate,'YYYY-MM-DD') ;

String formattedSQL = MessageFormat.format(SQL , new Object[] "sms.schema"});

and I am getting an ORA-00904: "DD": invalid identifier error when I run it. I am assuming this is because the msg_submit_date column has HH:MI:SS associated with it, however, I don't want to place HH:MI:SS in my java code. Having year, month and day will suffice.

Also, when I run the same query, in my Oracle SQL Developer IDE, it runs fine without any errors so not sure what is going wrong.

The date gets generated via java.time.LocalDate using the following format:

DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM/d/yyyy");

which returns the following string format 2018-06-03.

The jdbc call:

jdbcTemplate.query(formattedSQL, params, new MessagesRowMapper())

I think it maybe due to MessageFormat.

Show us your Java code. Edit your question - do not post code or additional information in comments. — user330315
– user330315, Commented Jul 12, 2018 at 18:04
The single quotes are being lost; is that really exactly how it looks, or is the entire string enclosed in double quotes? Does it need to be? (Also, why doesn't your ofPattern() match the to_date() pattern; and why are you passing strings instead of binding dates directly - doesn't Spring allow that?) — Alex Poole
– Alex Poole, Commented Jul 12, 2018 at 18:31
what about formattedSQL? How do you pass the values? Where is the code of the row mapper? — user330315
– user330315, Commented Jul 12, 2018 at 18:44

Alex Poole · Accepted Answer · 2018-07-12 18:45:32Z

2

The error is because the query doesn't have the single quotes when it is executed; you can get the same thing with:

SELECT msgs.msg_submit_date MSG_DATE FROM messages msgs
WHERE TRUNC(msgs.msg_submit_date)
BETWEEN TO_DATE(:startDate,YYYY-MM-DD) AND TO_DATE(:endDate,YYYY-MM-DD);

ERROR at line 3:
ORA-00904: "DD": invalid identifier

The date format components are being seen as identifiers, not as part of a string.

You need to figure out why that is happening - the most likely explanation is that you don't have them in the static string in your Java code, despite what you have posted in the question, but they may be being stripped out by a later transformation, e.g. by the MessageFormat.format() call.

answered Jul 12, 2018 at 18:45

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robin Over a year ago

Ty. It was due to MessageFormat stripping out single quotes. Not sure why it does that.

Gordon Linoff · Accepted Answer · 2018-07-12 17:48:23Z

0

You can write the query as:

SELECT msg_submit_date
FROM messages
WHERE submit_date >= DATE '2018-06-03' AND
      submit_date < DATE '2018-06-13';

Then you don't need to worry about any potential time component on the value.

That said, your problem appears to be with moving the data to your application. Perhaps this will help:

SELECT TRUNC(msg_submit_date)

answered Jul 12, 2018 at 17:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Date format returning ORA-00904 in java code but not in sql developer

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related