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
Advertisements

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