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"')