Design

Back Next

trunc (Dates)

Truncates dates to a specific year, month, day, hour, minute, second, or millisecond.

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 milliseconds 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 milliseconds 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.