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

For example, this is useful when performing a GROUP BY on DateTime data based on coarser time ranges (such as GROUP BY "Quarter" given DateTime values). The syntax of the function is:

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

For example, given a DateTime property "TimeStamp" with a value representing 10/13/2009 11:35:12.000, the TRUNC operator could be used to compute the following results (represented in pretty-printed form for clarity; actual results would use standard DateTime format):

TRUNC("TimeStamp", SECOND)  = 10/13/2009 11:35:12.000
TRUNC("TimeStamp", MINUTE)  = 10/13/2009 11:35:00.000
TRUNC("TimeStamp", HOUR)    = 10/13/2009 11:00:00.000
TRUNC("TimeStamp", DATE)    = 10/13/2009 00:00:00.000
TRUNC("TimeStamp", WEEK)    = 10/08/2009 00:00:00.000
TRUNC("TimeStamp", MONTH)   = 10/01/2009 00:00:00.000
TRUNC("TimeStamp", QUARTER) = 10/01/2009 00:00:00.000
TRUNC("TimeStamp", YEAR)    = 01/01/2009 00:00:00.000

As a simple example of using this functionality, the following query groups transaction records containing TimeStamp and Amount properties by quarter:

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