Week_Start and Week_End custom functions in Oracle, by Zahar Hilkevich

Frequently, there is a need to obtain a start and end dates of a week for a given date. The following SQL can be used to get the first day of the current week:

SELECT TRUNC(SYSDATE, 'DAY') week_start
FROM dual

Result:

WEEK_STAR
---------
25-AUG-13

Note, that TRUNC(SYSDATE, ‘IW’) will always give us Monday as ISO year (and IW format points to it) always starts on Monday, so we cannot generically use this format.

Getting a week’s end is even simpler:

SELECT TRUNC(SYSDATE, 'DAY') week_start,
       TRUNC(SYSDATE, 'DAY') + 6 week_end
FROM dual

Result:

WEEK_STAR WEEK_END
--------- ---------
25-AUG-13 31-AUG-13

or if we want to see the day of the week:

SELECT TO_CHAR(TRUNC(SYSDATE, 'DAY'), 'MM/DD/YY DAY') week_start,
       TO_CHAR(TRUNC(SYSDATE, 'DAY') + 6, 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ------------------
08/25/13 SUNDAY      08/31/13 SATURDAY

Now, let’s wrap it in PL/SQL functions:

CREATE OR REPLACE FUNCTION week_start(p_date DATE:=SYSDATE)
RETURN DATE
IS
BEGIN
    RETURN TRUNC(p_date, 'DAY');
END week_start;
/
CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE)
RETURN DATE
IS
BEGIN
    RETURN TRUNC(p_date, 'DAY')+6;
END week_end;
/
SELECT TO_CHAR(week_start, 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end, 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- -----------------
08/25/13 SUNDAY      08/31/13 SATURDAY

Now, let’s make things more complicated. What if a client wants a week to start with Saturday and end on Friday? For that matter, we will introduce a new function parameter: p_shift – it will control the shift from Sunday (or whatever your local setting is):

CREATE OR REPLACE FUNCTION week_start(p_date DATE:=SYSDATE, 
                                      p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    IF NOT ABS(p_shift) BETWEEN 0 AND 6 THEN
        RETURN NULL; --or raise exception
    END IF;
    RETURN TRUNC(p_date-p_shift, 'DAY')+p_shift;
END week_start;
/
CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE,
                                    p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    IF NOT ABS(p_shift) BETWEEN 0 AND 6 THEN
        RETURN NULL; --or raise exception
    END IF;
    RETURN TRUNC(p_date-p_shift, 'DAY')+p_shift+6;
END week_end;
/
SELECT TO_CHAR(week_start(p_shift=>1), 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end(p_shift=>1), 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ----------------
08/26/13 MONDAY      09/01/13 SUNDAY

The above functions can be created separately or together. If we always create both of them, we should eliminate redundant code and leverage existing:

CREATE OR REPLACE FUNCTION week_end(p_date DATE:=SYSDATE,
                                    p_shift NUMBER:=0)
RETURN DATE
IS
BEGIN
    RETURN week_start(p_date, p_shift)+6;
END week_end;
SELECT TO_CHAR(week_start(p_shift=>-1), 'MM/DD/YY DAY') week_start,
       TO_CHAR(week_end(p_shift=>-1), 'MM/DD/YY DAY')  week_end
FROM dual

Result:

WEEK_START           WEEK_END
-------------------- ----------------
08/24/13 SATURDAY    08/30/13 FRIDAY

Helpful reference:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm

Advertisements

2 thoughts on “Week_Start and Week_End custom functions in Oracle, by Zahar Hilkevich

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s