Using TRUNC to round down dateTime values

The TRUNC function can be used to round a dateTime value down to a coarser granularity.

For example, this may be useful when you want to group your statement results data for each quarter using a dateTime attribute.

The syntax of the TRUNC function is:

<TruncExpr>     ::=  TRUNC(<expr>,<DateTimeUnit>)
<dateTimeUnit>  ::=  SECOND | MINUTE | HOUR |
                     DATE | WEEK | MONTH | QUARTER | YEAR
                     DAY_OF_WEEK | DAY_OF_MONTH | DAY_OF_YEAR
                     JULIAN_DAY_NUMBER

Note: WEEK truncates to the nearest previous Sunday.

For example, the dateTime attribute TimeStamp has a value representing 10/13/2011 11:35:12.000. The list below shows the results of using the TRUNC operator to round the TimeStamp value at each level of granularity. The values are displayed here in a format that is easier to read—the actual values would use the standard Endeca dateTime format.

TRUNC("TimeStamp", SECOND)            = 10/13/2011 11:35:12.000
TRUNC("TimeStamp", MINUTE)            = 10/13/2011 11:35:00.000
TRUNC("TimeStamp", HOUR)              = 10/13/2011 11:00:00.000
TRUNC("TimeStamp", DATE)              = 10/13/2011 00:00:00.000
TRUNC("TimeStamp", WEEK)              = 10/09/2011 00:00:00.000
TRUNC("TimeStamp", MONTH)             = 10/01/2011 00:00:00.000
TRUNC("TimeStamp", QUARTER)           = 10/01/2011 00:00:00.000
TRUNC("TimeStamp", YEAR)              = 01/01/2011 00:00:00.000
TRUNC("TimeStamp", DAY_OF_WEEK)       = 10/13/2011 00:00:00:000
TRUNC("TimeStamp", DAY_OF_MONTH)      = 10/13/2011 00:00:00:000
TRUNC("TimeStamp", DAY_OF_YEAR)       = 10/13/2011 00:00:00:000
TRUNC("TimeStamp", JULIAN_DAY_NUMBER) = 10/13/2011 00:00:00:000

Here is a simple example of using this functionality. In the following statement, the total value for the Amount attribute is grouped by quarter. The quarter is obtained by using the TRUNC operation on the TimeStamp attribute:

RETURN Quarters AS
SELECT SUM(Amount) AS Total,
       TRUNC(TimeStamp, QUARTER) AS Qtr
GROUP BY Qtr