How to change the day that Oracle TRUNC uses to start the week

Question

I have the following SQL:

SELECT SUM(theNumberCol) qty, TRUNC(theDateCol, 'WW') weekDate
FROM theTable
GROUP BY TRUNC(theDateCol, 'WW');

This works fine for finding the sum of theNumberCol for each week. The problem is that the week seems to start on a Tuesday. For example "12/17/2013 8:56:05 AM" is truncated to "12/17/2013" while "12/16/2013 5:09:25 AM" is truncated to "12/10/2013".

How does Oracle determine which day to start the week?
Can I change the week to start on Saturday?

score 2 · Answer 1 · answered Dec 14 '13 at 04:39

2

There are two different calculations available: classic oracle, which calculates week=int(dayOfYear+6)/7, and iso mode, which uses ISO 8601. Format WW uses the classic calculation, while format IW uses the ISO standard.

So as you see, WW does not start the week on any fixed day of the week, it just starts the week on january 1st, whichever day that is. IW is what should work for you if you live in any country that follows international standards.

answered Dec 14 '13 at 04:39

Guntram Blohm

9,667
2
24
31

Except the OP needs **Saturday** to 'start' the week, and ISO starts with **Monday**. – Clockwork-Muse Dec 14 '13 at 04:46

score 1 · Answer 2 · answered Dec 14 '13 at 07:33

1

It depends on your local NLS_TERRITORY which is the first day. E.g. 'germany' will return monday. 'america' returns sunday, 'egypt' returns saturday.

Use alter session set nls_territory ='germany'; for example

answered Dec 14 '13 at 07:33

Wernfried Domscheit

54,457
9
76
110

+1: docs ... http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions242.htm#SQLRF52038 – David Aldridge Dec 14 '13 at 09:10

score 1 · Accepted Answer · answered Dec 14 '13 at 13:48

Take 7 days away from the truncated date and then find the next Saturday from that date:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE theTable ( theNumberCol, theDateCol ) AS
          SELECT  1, TO_DATE( '20131202 23:15:52', 'YYYYMMDD HH24:MI:SS' ) FROM DUAL
UNION ALL SELECT  2, TO_DATE( '20131203', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  3, TO_DATE( '20131204', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  4, TO_DATE( '20131205', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  5, TO_DATE( '20131206', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  6, TO_DATE( '20131207', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  7, TO_DATE( '20131208', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  8, TO_DATE( '20131209', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT  9, TO_DATE( '20131210', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 10, TO_DATE( '20131211', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 11, TO_DATE( '20131212', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 12, TO_DATE( '20131213', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 13, TO_DATE( '20131214', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 14, TO_DATE( '20131215', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 15, TO_DATE( '20131216', 'YYYYMMDD' ) FROM DUAL
UNION ALL SELECT 16, TO_DATE( '20131217', 'YYYYMMDD' ) FROM DUAL;

Query 1:

SELECT SUM(theNumberCol) AS qty,
       NEXT_DAY( TRUNC( theDateCol ) - INTERVAL '7' DAY, 'SATURDAY' ) weekDate
FROM   theTable
GROUP BY
       NEXT_DAY( TRUNC( theDateCol ) - INTERVAL '7' DAY, 'SATURDAY' )
ORDER BY
       weekDate ASC

Results:

| QTY |                        WEEKDATE |
|-----|---------------------------------|
|  15 | November, 30 2013 00:00:00+0000 |
|  63 | December, 07 2013 00:00:00+0000 |
|  58 | December, 14 2013 00:00:00+0000 |

Beautiful! This also solves the problem of missing weeks when there are no dates during week interval. — styfle, Dec 16 '13 at 18:23

score 0 · Answer 4 · edited May 23 '17 at 11:58

I don't know that changing the behavior of TRUNC(...) is possible (and having it deviate from the documentation is likely to cause problems). However, there is another solution (one that might perform better):

You want what's known as a Calendar Table (example is SQL Server, but the basic concept applies). If you don't have one, I recommend creating (and indexing) one. For example, regardless of if your week starts on a Saturday, using the calendar table then makes this trivial:

SELECT
FROM WeekRange.weekStart, SUM(theTable.theNumberCol)
JOIN (SELECT dateValue as weekStart, dateValue + 7 as nextWeekStart
      FROM Calendar
      WHERE nameOfDay = 'Saturday') WeekRange
  ON theTable.theDateCol >= WeekRange.weekStart 
     AND theTable.theDateCol < WeekRange.nextWeekStart
GROUP BY WeekRange.weekStart

Add/replace conditions as necessary, of course. This version is also more likely to use any indices over the relevant columns.

How to change the day that Oracle TRUNC uses to start the week

4 Answers4