Using SQL Commands
The following examples demonstrate how SQL Commands can be used in the DB Writer.
Since functions and to some extent the syntax are specific to the server on which they are invoked.
Without Field References
The following function would return the current date on a SQL server.
GETDATE()
The following function would return the current date on a Postgres server.
The current_date function does not require brackets and indeed would cause an error if brackets were specified like above.
current_date
With Field References
Field references within SQL Commands allow you to embed values from IMan. It is typically necessary to enclose the field name in square brackets. This is necessary since there is often a trailing character to close the function or specify another argument (via a comma) in the function.
Single Field Reference
The following function converts a value to a Date on an Oracle database.
TO_DATE(%[OrderDate])
Multiple Field References
The following function adds a number of days (DaysToShip) to a Date (OrderDate).
DATEADD(d, %[DaysToShip], %[OrderDate])
Mixture of Functions and Syntax
The following function concatenates the current date on the server to a value (CustomerNote) from IMan.
GETDATE() + ' - ' + %[CustomerNote]
Using the Oracle TO_DATE & TO_TIMESTAMP functions
The easiest way to handle date and date/time values on Oracle databases are to use the TO_DATE & TO_TIMESTAMP functions.
Converting to a Date Value
TO_DATE(%[ShortDateOnlyField],'DD/MM/YYYY')
Converting a Date & Time Value
The following takes a value which has both date and time to reformat it to something which can be accepted by Oracle.
TO_TIMESTAMP(%[DateAndTimeField],'YYYY-MM-DD"T"HH24:MI:SS.FF"Z"')