0

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Mahmoud
  • 325
  • 10
  • 25

1 Answers1

2

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')
...
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I change my query to `AND (to_date(DateTrasaction,'YYYY/MM/DD HH24:MI:SS') between to_date('01/01/2016','YYYY/MM/DD HH24:MI:SS') and to_date('29/02/2016','YYYY/MM/DD HH24:MI:SS'))` Know I don't have error but the resut is always 0 Rows – Mahmoud Feb 15 '16 at 23:53
  • @Mahmoud - that will error because you have the day and year swapped over. – Alex Poole Feb 15 '16 at 23:56
  • Yeaaaah; you're right it's working know `AND (to_date(DateTrasaction,'YYYY/MM/DD HH24:MI:SS') between to_date('01/01/2016','dd/MM/yyyy hh24:mi:ss') and to_date('29/02/2016','dd/MM/yyyy hh24:mi:ss'))` thank you very much – Mahmoud Feb 15 '16 at 23:59
  • Why do you have `date_transaction` as varchar? It should be a date if it holds dates. Maybe the entries in that are not formatted properly. If it was the right data type that would not be an issue... Also be aware that your `between` will not include anything after midnight on Feb 29th. Doing >= 01/01/2016 and < 01/03/2016 is safer/clearer. – Alex Poole Feb 16 '16 at 00:00