Oracle Update sysdate problems

What datatype is date_start?

It really should be a Date or DateTime, and you are trying to insert a string ( to_char ). You should never ever store dates and datetimes as strings. That it works in sqlplus could very well be because either SQLPlus is implicitly converting, or Oracle is, and the way FME tries to update probably prevents the implicit conversion (because of parameterized queries).

Hi ​@s.jager Datetype in the databases is a DATE

And yet you are trying to update it with a string.

Try simply sysdate-1, or even trunc(sysdate -1).

But do not use the to_char...

still not working

 sysdate-1 works in oracle, BUt not with FME

With SQLCreator/SQLExecutor, to diagnose you could check that the value is being parsed and calculated properly form the SQL string by

SELECT sysdate AS test_date_1 , sysdate-1 AS test_date_2 FROM <table_name>

This will at least suggest if the sysdate expressions are returning expected date values (in FME it should convert these into fme datetime format in the returned features from SQLCreator/SQLCreator)

If this returns the values fine, the problem may be be more in the attempted update operation itself rather than sysdate.

This code works for me

UPDATE MY_TABLE SET DATE_TODAY = (SYSDATE-1)

as does this

FME_SQL_DELIMITER ;
UPDATE MY_TABLE SET DATE_TODAY = (SYSDATE-1);
COMMIT;

Hi

i know:  FME_SQL_DELIMITER ; UPDATE MY_TABLE SET DATE_TODAY = (SYSDATE-1); COMMIT;

this works

but not with sysdate -1,'dd/mm/yyyy'

But I fix this by:

1. attributorCreator _dateYesterday  =   @DateTimeFormat(@DateTimeAdd(@DateTimeNow(),-P1D),%Y%m%d)
2. DateTimeConverter  output %d/%m/%Y
3. SQL executor  TO_DATE('@Value(_dateYesterday)','DD/MM/YYYY')

That works