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.
<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
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
RETURN Quarters AS SELECT SUM(Amount) AS Total, TRUNC(TimeStamp, QUARTER) AS Qtr GROUP BY Qtr