to_date me donne error ORA-01861: literal does not match format string
Hello j'essaye d'utiliser java pour filtrer une requete sql sous talend mais la base de donnée oracle me retourne ORA-01861: literal does not match format string
Voici mon code java :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
Date supply_date_last_filter = new Date();
Date supply_date_start_filter;
supply_date_last_filter = TalendDate.parseDate("ddMMyyyy",TalendDate.formatDate("ddMMyyyy", new Date()));
if (context.work_METI_last_date != null && !"".equals(context.work_METI_last_date)) {
supply_date_last_filter = TalendDate.parseDate("ddMMyyyy",context.work_METI_last_date);
}
supply_date_start_filter = TalendDate.addDate(supply_date_last_filter, - Integer.parseInt(context.work_METIstore_last_days), "dd");
String supply_date_start_filter_str = TalendDate.formatDate("ddMMyyyy",supply_date_start_filter);
String supply_date_last_filter_str = TalendDate.formatDate("ddMMyyyy",supply_date_last_filter);
globalMap.put("supply_date_start_filter", supply_date_start_filter_str);
globalMap.put("supply_date_last_filter", supply_date_last_filter_str); |
Et voici ma requete oracle sous talend :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
"SELECT substr(fld_znvaleur, 5, 3) store_code, substr(fld_znvaleur, 8, 8) order_number, substr(fld_znvaleur, 16, 8) order_date, std_cdsitu, std_lbsitu, wcd_tydemand, tyd_lbtypdem
FROM " + context.link_METIsupply_DB_schema + ".mgfld
INNER JOIN " + context.link_METIsupply_DB_schema + ".mgfle
ON (fle_cdflux = fld_cdflux
AND fle_notrait = fld_notrait)
LEFT JOIN " + context.link_METIsupply_DB_schema + ".mgwcd
ON (wcd_nocdemag = to_number(substr(fld_znvaleur, 8, 8))
AND wcd_noclcash = to_number(substr(fld_znvaleur, 5, 3)))
LEFT JOIN " + context.link_METIsupply_DB_schema + ".mgstd
ON (wcd_tydemand = std_tydemand and wcd_cdetat = std_cdsitu)
LEFT JOIN " + context.link_METIsupply_DB_schema + ".mgtyd
ON (tyd_tydemand = wcd_tydemand)
WHERE fld_cdflux = 'DDE'
AND fld_cdfichier = 'DDE_E'
AND substr(fld_znvaleur, 16, 8) BETWEEN to_date('" + ((String) globalMap.get("supply_date_start_filter")) + "','DDMMYYYY')
AND to_date('" + ((String) globalMap.get("supply_date_last_filter")) + "','DDMMYYYY')
and substr(fld_znvaleur, 16, 8) <> ' '
AND substr(fld_znvaleur, 5, 3) in " + context.work_METIstore_list |
Je suis un peu perdu là car je donne bien un string de la bonne longeur à la fonction to_date...
ps les quote au début et à la fin sont obligatoire sous talend.
