Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

SYSTIMESTAMP

Returns the current date and time with precision to the nanosecond of the system that hosts the Secure Agent that starts the task. The precision to which you can retrieve the date and time depends on the system that hosts the Secure Agent.

The return value of the function varies depending on how you configure the argument:

When you configure the argument of SYSTIMESTAMP as a variable,

Data Integration

evaluates the function for each row in the transformation.

When you configure the argument of SYSTIMESTAMP as a constant,

Data Integration

evaluates the function once and retains the value for each row in the transformation.

Syntax

SYSTIMESTAMP( [format] )

Argument	Required/ Optional	Description
format	Optional	Precision to which you want to retrieve the timestamp. You can specify precision up to seconds (SS), milliseconds (MS), microseconds (US), or nanoseconds (NS). Enclose the format string within single quotation marks. The format string is not case sensitive. For example, to display the date and time to the precision of milliseconds use the following syntax: SYSTIMESTAMP(‘MS’). Default precision is microseconds (US).

Return Value

Timestamp. Returns date and time to the specified precision. Precision dependent on platform.

Examples

Your organization has an online order service and processes real-time data. You can use the SYSTIMESTAMP function to generate a primary key for each transaction in the target database.

Create an Expression transformation with the following fields and values:

Field Name	Field Type	Expression
Customer_Name	Input	n/a
Order_Qty	Input	n/a
Time_Counter	Variable	'US'
Transaction_ID	Output	SYSTIMESTAMP (Time_Counter)

At run time, the SYSTIMESTAMP generates the system time to the precision of microseconds for each row:

Customer_Name	Order_Qty	Transaction_Id
Vani Deed	14	07/06/2007 18:00:30.701015000
Kalia Crop	3	07/06/2007 18:00:30.701029000
Vani Deed	6	07/06/2007 18:00:30.701039000
Harry Spoon	32	07/06/2007 18:00:30.701048000