Recommendations of ODI variables to store date values

It is highly recommended by the Oracle to use Alphanumeric variable to store date values.


And to refresh this type of variable use the following technology dependent statement.


ORACLE: 




SELECT sysdate FROM dual




Microsoft SQL server:




SELECT getdate()




Later when the variable is used in another step, use syntax such as


Oracle:


UPDATE MY_TABLE WHERE MY_DATE=TO_DATE('#PROJ.MY_VAR1','DD-MM-YYYY HH24:MI:SS')




If an ODI 'Date' type variable is required keep in mind that the value will be stored in the following java format




yyy-mm-dd hh:mm:ss.o




When you need to retrive the value from the variable within an integration interface, you are required to alter the date format using SQL instructions inorder to match the fromat expected by RDBMS.