Table of Contents

Search

  1. Preface
  2. Taskflows and linear taskflows
  3. Taskflows
  4. Linear taskflows

Taskflows

Taskflows

trunc (Dates)

trunc (Dates)

Truncates dates to a specific year, month, day, hour, minute, second, or millisecond. 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:
    date:trunc(xs:dateTime('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:
    date:trunc(xs:dateTime('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:
    date:trunc(xs:dateTime('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:
    date:trunc(xs:dateTime('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:
    date:trunc(xs:dateTime('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:
    date:trunc(xs:dateTime('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:
    date:trunc(xs:dateTime('5/22/1997 10:15:30.135235'), 'MS')

Syntax

date:trunc(xs:dateTime('date'), 'format')
You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.
The following table describes the arguments:
Argument
Required/
Optional
Description
date
Required
Date/Time data type. The date values that you want to truncate. You can enter any valid transformation expression that evaluates to a date.
To pass a NULL value, you must specify an empty sequence in the following format:
()
format
Required
Enter a valid format string. The format string is not case sensitive.
To pass a NULL value, you must specify an empty sequence in the following format:
()

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 column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'Y') date:trunc(xs:dateTime('DATE_SHIPPED'), 'YY') date:trunc(xs:dateTime('DATE_SHIPPED'), 'YYY') date:trunc(xs:dateTime('DATE_SHIPPED'), 'YYYY')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 00:00:00.000000000
Apr 19 1998 1:31:20PM
Jan 1 1998 00:00:00.000000000
Jun 20 1998 3:50:04AM
Jan 1 1998 00:00:00.000000000
Dec 20 1998 3:29:55PM
Jan 1 1998 00:00:00.000000000
NULL
NULL
The following expressions truncate the month portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MM') date:trunc(xs:dateTime('DATE_SHIPPED'), 'MON') date:trunc(xs:dateTime('DATE_SHIPPED'), 'MONTH')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 1 1998 00:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 1 1998 00:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 1 1998 00:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 1 1998 00:00:00.000000000
NULL
NULL
The following expressions truncate the day portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'D') date:trunc(xs:dateTime('DATE_SHIPPED'), 'DD') date:trunc(xs:dateTime('DATE_SHIPPED'), 'DDD') date:trunc(xs:dateTime('DATE_SHIPPED'), 'DY') date:trunc(xs:dateTime('DATE_SHIPPED'), 'DAY')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 00:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 00:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 00:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 00:00:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 00:00:00.000000000
NULL
NULL
The following expressions truncate the hour portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'HH') date:trunc(xs:dateTime('DATE_SHIPPED'), 'HH12') date:trunc(xs:dateTime('DATE_SHIPPED'), 'HH24')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:31AM
Jan 15 1998 02:00:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 13:00:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 03:00:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 15:00:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 23:00:00.000000000
NULL
NULL
The following expression truncates the minute portion of each date in the DATE_SHIPPED column:
date:trunc(xs:dateTime('DATE_SHIPPED'), 'MI')
The following table lists some sample values and return values:
DATE_SHIPPED
RETURN VALUE
Jan 15 1998 2:10:30AM
Jan 15 1998 02:10:00.000000000
Apr 19 1998 1:31:20PM
Apr 19 1998 13:31:00.000000000
Jun 20 1998 3:50:04AM
Jun 20 1998 03:50:00.000000000
Dec 20 1998 3:29:55PM
Dec 20 1998 15:29:00.000000000
Dec 31 1998 11:59:59PM
Dec 31 1998 23:59:00.000000000
NULL
NULL

0 COMMENTS

We’d like to hear from you!