Preface
The Transformation Language
Constants
Operators
Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Binary Functions
  - Character Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Variable Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ASCII
- AVG
- BINARY_COMPARE
- BINARY_CONCAT
- BINARY_LENGTH
- BINARY_SECTION
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DEC_HEX
- DECODE
- DECOMPRESS
- EBCDIC_ISO88591
- ENC_BASE64
- ENC_HEX
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOOKUP
- 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
- SETCOUNTVARIABLE
- SET_DATE_PART
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
Creating Custom Functions
Custom Function API Reference

Transformation Language Reference

10.5.1
- 10.5.6
- 10.5
- 10.4.0

Back Next

MAKE_DATE_TIME

Returns the date and time based on the input values.

Syntax

MAKE_DATE_TIME( year, month, day, hour, minute, second, nanosecond )

The following table describes the arguments for this command:

Argument	Required/ Optional	Description
year	Required	Numeric datatype. Positive 4-digit integer. If you pass this function a 2-digit year, the PowerCenter Integration Service returns “00” as the first two digits of the year.
month	Required	Numeric datatype. Positive integer between 1 and 12 (January=1 and December=12). The Spark engine writes null values for rows when the month argument in the MAKE_DATE_TIME function passes an invalid value. In the native environment, the Data Integration Service rejects the row and does not write it to the target.
day	Required	Numeric datatype. Positive integer between 1 and 31 (except for the months that have less than 31 days: February, April, June, September, and November).
hour	Optional	Numeric datatype. Positive integer between 0 and 24 (where 0=12AM, 12=12PM, and 24 =12AM).
minute	Optional	Numeric datatype. Positive integer between 0 and 59.
second	Optional	Numeric datatype. Positive integer between 0 and 59.
nanosecond	Optional	Numeric datatype. Positive integer between 0 and 999,999,999.

Return Value

Date as MM/DD/YYYY HH24:MI:SS. Returns a null value if you do not pass the function a year, month, or day.

Example

The following expression creates a date and time from the input ports:

MAKE_DATE_TIME( SALE_YEAR, SALE_MONTH, SALE_DAY, SALE_HOUR, SALE_MIN, SALE_SEC )

SALE_YR	SALE_MTH	SALE_DAY	SALE_HR	SALE_MIN	SALE_SEC	RETURN VALUE
2002	10	27	8	36	22	10/27/2002 08:36:22
2000	6	15	15	17		06/15/2000 15:17:00
2003	1	3		22	45	01/03/2003 00:22:45
04	3	30	12	5	10	03/30/0004 12:05:10
99	12	12	5		16	12/12/0099 05:00:16

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

MAKE_DATE_TIME

MAKE_DATE_TIME

Syntax

Return Value

Example