Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

TRUNC (Dates)

TRUNC (Dates)

Truncates dates to a specific year, month, day, hour, minute, second, millisecond, or microsecond. You can also use TRUNC to truncate numbers.
You can truncate the following date parts:
  • Year.
    If you truncate the year portion of the date, the function returns Jan 1 of the input year with the time set to 00:00:00.000000000. For example, the following expression returns 1/1/1997 00:00:00.000000000:
    TRUNC(12/1/1997 3:10:15, 'YY')
  • Month.
    If you truncate the month portion of a date, the function returns the first day of the month with the time set to 00:00:00.000000000. For example, the following expression returns 4/1/1997 00:00:00.000000000:
    TRUNC(4/15/1997 12:15:00, 'MM')
  • Day.
    If you truncate the day portion of a date, the function returns the date with the time set to 00:00:00.000000000. For example, the following expression returns 6/13/1997 00:00:00.000000000:
    TRUNC(6/13/1997 2:30:45, 'DD')
  • Hour.
    If you truncate the hour portion of a date, the function returns the date with the minutes, seconds, and subseconds set to 0. For example, the following expression returns 4/1/1997 11:00:00.000000000:
    TRUNC(4/1/1997 11:29:35, 'HH')
  • Minute.
    If you truncate the minute portion of a date, the function returns the date with the seconds and subseconds set to 0. For example, the following expression returns 5/22/1997 10:15:00.000000000:
    TRUNC(5/22/1997 10:15:29, 'MI')
  • Second.
    If you truncate the second portion of a date, the function returns the date with the milliseconds set to 0. For example, the following expression returns 5/22/1997 10:15:29.000000000:
    TRUNC(5/22/1997 10:15:29.135, 'SS')
  • Millisecond.
    If you truncate the millisecond portion of a date, the function returns the date with the microseconds set to 0. For example, the following expression returns 5/22/1997 10:15:30.135000000:
    TRUNC(5/22/1997 10:15:30.135235, 'MS')
  • Microsecond.
    If you truncate the microsecond portion of a date, the function returns the date with the nanoseconds set to 0. For example, the following expression returns 5/22/1997 10:15:30.135235000:
    TRUNC(5/22/1997 10:15:29.135235478, 'US')

Syntax

TRUNC(
date
[,
format
] )
The following table describes the arguments for this command:
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. The date values you want to truncate. You can enter any valid transformation expression that evaluates to a date.
format
Optional
Enter a valid format string. The format string is not case sensitive. If you omit the format string, the function truncates the time portion of the date, setting it to 00:00:00.000000000.

Return Value

Date.
NULL if a value passed to the function is NULL.

Examples

The following expressions truncate the year portion of dates in the DATE_SHIPPED port:
TRUNC( DATE_SHIPPED, 'Y' ) TRUNC( DATE_SHIPPED, 'YY' ) TRUNC( DATE_SHIPPED, 'YYY' ) TRUNC( DATE_SHIPPED, 'YYYY' )
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 12:00:00.000000000
Apr 19 1998 1:31:20PM
Jan 1 1998 12:00:00.000000000
Jun 20 1998 3:50:04AM
Jan 1 1998 12:00:00.000000000
Dec 20 1998 3:29:55PM
Jan 1 1998 12:00:00.000000000
NULL
NULL
The following expressions truncate the month portion of each date in the DATE_SHIPPED port:
TRUNC( DATE_SHIPPED, 'MM' ) TRUNC( DATE_SHIPPED, 'MON' ) TRUNC( DATE_SHIPPED, 'MONTH' )
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 12:00:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 1 1998 12:00:00.000000000AM
Jun 20 1998 3:50:04AM
Jun 1 1998 12:00:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 1 1998 12:00:00.000000000AM
NULL
NULL
The following expressions truncate the day portion of each date in the DATE_SHIPPED port:
TRUNC( DATE_SHIPPED, 'D' ) TRUNC( DATE_SHIPPED, 'DD' ) TRUNC( DATE_SHIPPED, 'DDD' ) TRUNC( DATE_SHIPPED, 'DY' ) TRUNC( DATE_SHIPPED, 'DAY' )
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 12:00:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 19 1998 12:00:00.000000000AM
Jun 20 1998 3:50:04AM
Jun 20 1998 12:00:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 20 1998 12:00:00.000000000AM
Dec 31 1998 11:59:59PM
Dec 31 1998 12:00:00.000000000AM
NULL
NULL
The following expressions truncate the hour portion of each date in the DATE_SHIPPED port:
TRUNC( DATE_SHIPPED, 'HH' ) TRUNC( DATE_SHIPPED, 'HH12' ) TRUNC( DATE_SHIPPED, 'HH24' )
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:31AM
Jan 15 1998 2:00:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 19 1998 1:00:00.000000000PM
Jun 20 1998 3:50:04AM
Jun 20 1998 3:00:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 20 1998 3:00:00.000000000PM
Dec 31 1998 11:59:59PM
Dec 31 1998 11:00:00.000000000AM
NULL
NULL
The following expression truncates the minute portion of each date in the DATE_SHIPPED port:
TRUNC( DATE_SHIPPED, 'MI' )
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 2:10:00.000000000AM
Apr 19 1998 1:31:20PM
Apr 19 1998 1:31:00.000000000PM
Jun 20 1998 3:50:04AM
Jun 20 1998 3:50:00.000000000AM
Dec 20 1998 3:29:55PM
Dec 20 1998 3:29:00.000000000PM
Dec 31 1998 11:59:59PM
Dec 31 1998 11:59:00.000000000PM
NULL
NULL