List Remaining Days of the Week with SQL

SQL Puzzle of the day:

List all days from today till the last day of the week. The query should work regardless of the regional settings that affect first day of the week, whether it is Sunday, Monday, or any other day.

The trick here is not to attempt figuring out the current day of the week, whether it is Friday, Saturday or anything else. We need to apply a date function that returns the same value (or is constantly staying in the same interval) for all the days within the same week. The following 3 strategies are all based on such functions: TRUNC and TO_CHAR.

Strategy #1: Using TRUNC with ‘D’ format

SELECT SYSDATE + LEVEL - 1 AS Day
FROM dual
CONNECT BY TRUNC(SYSDATE, 'D') = TRUNC(SYSDATE + LEVEL - 1, 'D')

Strategy #2: Using SIGN and TO_CHAR with ‘D’ format

SELECT SYSDATE + LEVEL - 1 AS Day
FROM dual
CONNECT BY CONNECT BY 
   SIGN(TO_CHAR(SYSDATE + LEVEL - 1, 'D') -
        TO_CHAR(SYSDATE + LEVEL - 2, 'D')) =1

Strategy #3: Using TRUNC and calculating the week end by adding 7 to the first day

SELECT SYSDATE + LEVEL - 1 AS Day
FROM dual
CONNECT BY TRUNC(SYSDATE+LEVEL)-TRUNC(SYSDATE,'D')<=7

Here is a useful link to Oracle documentation that explains different format strings:

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/ROUND-and-TRUNC-Date-Functions.html#GUID-8E10AB76-21DA-490F-A389-023B648DDEF8

***

If you find this post useful, please press the LIKE button and subscribe.

My Oracle Group on Facebook:

Also, you may want to join my Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Suggested Reading:

Would you like to read about many more tricks and puzzles? For more clever tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

 

How to Mimic Oracle’s SYSDATE function in Teradata

There is no direct counterpart of Oracle’s SYSDATE function in Teradata. There is CURRENT_DATE and DATE(both return date only), CURRENT_TIME (time only), CURRENT_TIMESTAMP (similar to SYSTIMESTAMP in Oracle), but in Order to mimic SYSDATE you need to concatenate results of two functions:

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0))

or

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0))

The following function will encapsulate the above expression and give you a feeling of using Oracle’s SYSDATE function:

REPLACE FUNCTION SYSDATE()
   RETURNS TIMESTAMP
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Now, you can use it as follows:

SELECT SYSDATE()

Result:
03/24/2016 12:25:23

List all weekend days for the current month

Problem: List all weekend days for the current month

Problem Level: Beginner/Intermediate

Solution:

We will break down the problem into 2 parts:
1) Get all days for the current month
2) Selecting only weekends

1) Get all days for the current month

Selecting all days from the current months requires one of two things:

• Knowing the first day of the month
• Knowing how to check for the end of the month or how to get the number of days in the month

The first task is fairly simple. We can use TRUNC function to get the first day of the month as follows:

SELECT TRUNC(SYSDATE, 'MONTH') first_day
FROM DUAL

End of the month can be found by adding one month and subtracting one day:

SELECT TRUNC(SYSDATE, 'MONTH') first_day,
       ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)-1 last_day
FROM dual

Now, if you know how to generate a numeric range, you can also generate the date range as follows:

SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(SYSDATE, 'MONTH')+LEVEL<=ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'), 1)

Alternatively, you can control the end of the month by checking if the “current level’s day” has the same month:

SELECT TRUNC(SYSDATE, 'MONTH') + LEVEL - 1 as "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH') 

2) Selecting only weekends

In the following post I have altready demonstrated how to reliably get the first and last week days (i.e. weekend), so applying the method presenting in that post to all days in the current month should accomplish our goal:

Week_Start and Week_End custom functions in Oracle

WITH days AS (
SELECT TRUNC(SYSDATE, 'MONTH')+LEVEL-1 AS "day"
FROM dual
CONNECT BY TRUNC(TRUNC(SYSDATE, 'MONTH')+LEVEL-1, 'MONTH')=TRUNC(SYSDATE, 'MONTH')
)
SELECT "day", TO_CHAR("day", 'Day') "day of the week"
FROM days
WHERE "day" IN (TRUNC("day", 'DAY'), TRUNC("day", 'DAY')+6)

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

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