Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

MAKE_DATE_TIME

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
Data 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

0 COMMENTS

We’d like to hear from you!