Customised DATE Trunc command

Re all,

I need to use the TRUNC(date, '...') command to group data into separate groups.

The problem I have is that the default options for the TRUNC command are not the ones that I want. Instead, I need to start a day at 6am rather than at midnight (which is the Oracle default for the TRUNC command), and ends at 5:59am on the next day.

Also I need to change this to handle, WEEKS (start on Monday rather than Sunday (Oracle default)), MONTHS, YEARS. All these options need to start at 6am on the first day within that time period.

I was wondering if anyone has an efficient way of producing a solution to this problem.

My first thought was have a function that takes the actual date and the period I want, then returns the date truncated to the appropriate date. This function would have to be used within the SELECT clause of an SQL statement, so I would do a pragma restrict_references on it. And it would also be used within the GROUP BY clause of the same select statement.

When I think of this, I think: My God, that is going to be one hell of a slow SQL statement.

Does anyone have any better suggestions or solutions to my problem.

Thanks.
Paul J Received on Wed Oct 11 2000 - 20:23:21 CDT