CODEWITHSUNDEEP
javasqloracle-database
devlopp
devlopp

Reputation: 437

java.sql.SQLException: ORA-01843: not a valid month (Working in SQL DEVELOPER and doesn't work in JAVA)

I have a table in database that contains the 'Date_transaction' column his type is varchar.

In my Code JAVA, I create a SQL query via several conditions.

When I debug in Eclipse the query generated is like this:

SELECT *
FROM Transaction  where 1=1  
AND  (to_date(Date_transaction,'YYYY/MM/DD HH:MI:SS') between '16/01/01' and    '16/02/29')  
AND projet = 'Project name'  
AND nomtranche = 'tranche name' AND voletctrl = 'volet name' 
AND (numeroimmeuble BETWEEN 1 AND 100)  
AND validation = 1  
AND statutDocNormal = 'statut'  
AND numeroAppartement = 14  
order by DateTrasaction DESC;

I execute this query in SQL DEVELOPER, the query is executed successfully without any error.

But in my code Java I get this Error : java.sql.SQLException: ORA-01843: not a valid month.

When I want to generate the query, I use this method to convert my date, this I spend in parameter (In the query it's : 16/01/01 and 16/02/29):

    public static String parseDate2(Date date) {
        SimpleDateFormat sdf = new SimpleDateFormat("yy/MM/dd");
        String dt = sdf.format(date);
        return dt;
    }

I try this answer but it's not working.

Upvotes: 0

Views: 7987

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You are relying on the session's NLS_DATE_FORMAT, which is set differently in the client and probably indirectly via your Java locale. Use explicit conversion with a specific format mask:

... between to_date('16/01/01', 'RR/MM/DD') and to_date('16/02/29', 'RR/MM/DD') ...

But it would be better to use four-digit years and YYYY (remember Y2K?), or date literals - those those don't work with variable values.

This also looks wrong:

to_date(Date_transaction,'YYYY/MM/DD HH:MI:SS')

If `date_transaction is already a date then you are implicitly converting it to a string and then back to a date, which is pointless and dangerous. And then possibly back to a string to compare with your fixed values. If it is a string then it shouldn't be. Either way you need HH24 rather than just HH so you can distinguish between AM and PM.

If it is a date you need:

...
date_transaction between to_date('2016/01/01', 'YYYY/MM/DD')
  and to_date('2016/02/29', 'YYYY/MM/DD')
...

Upvotes: 2

Related Questions