I'm trying to store the sysdate to an oracle database table; i have already acquired the system date by
Timestamper
with format "FME Date/Time (^Y^m^d^H^M^S)" and setting my attribute. I get an error when the process is trying to wrtite this attribute to the oracle table. Do I need to use some transformer or oracle string conversion function ? (like to_date('@mysysdate', 'YYYYMMDD')
thanks in advance
Usually (depending on the settings for your database) Oracle requires dates to be written using the format DD-MON-YYYY, in the DateFormatter %d-%b-%Y.
NLS_DATE_FORMAT
You need to conform to this setting if you write(to) attributes with dateformat.
You can query the attribute to find out wich format is used.
SELECT * FROM V$NLS_PARAMETERS
(for all of em)Or if you do not have the rights to access the data dictionary view mentioned by @gio yu can select the system date by using 'Select sysdate from dual;' and view the required formatting.
If your attribute value looks like 20160411104915 and your date column is defined as a DATE, you have nothing to do and FME should take care of doing the proper call to Oracle (if you are using a feature writer).
If you are using SQLExecutor, you have to use the TO_DATE function like in the following query when your date attribute is mysysdate:
INSERT INTO MY_TABLE (DATE_COLUMN)
VALUES (TO_DATE(@Value(mysysdate), 'YYYYMMDDHH24MISS'))
What is the error message you are receiving and what is the value of the attribute if you log the feature just before writing it to Oracle?
If your attribute value looks like 20160411104915 and your date column is defined as a DATE, you have nothing to do and FME should take care of doing the proper call to Oracle (if you are using a feature writer).
If you are using SQLExecutor, you have to use the TO_DATE function like in the following query when your date attribute is mysysdate:
INSERT INTO MY_TABLE (DATE_COLUMN)
VALUES (TO_DATE(@Value(mysysdate), 'YYYYMMDDHH24MISS'))
What is the error message you are receiving and what is the value of the attribute if you log the feature just before writing it to Oracle?
Agreed, this perfectly reflects my experience. I've never had to take NLS_DATE_FORMAT into account using this strategy.