Some more Date Functions

To extract each element of a Timestamp variable

SELECT
EXTRACT(year FROM SYSTIMESTAMP) year,
EXTRACT(month FROM SYSTIMESTAMP) month,
EXTRACT(day FROM SYSTIMESTAMP) dat,
EXTRACT(hour FROM SYSTIMESTAMP) hour,
EXTRACT(minute FROM SYSTIMESTAMP) minute,
EXTRACT(second FROM SYSTIMESTAMP) second,
EXTRACT(timezone_hour FROM SYSTIMESTAMP) tz_hour,
EXTRACT(timezone_minute FROM SYSTIMESTAMP) tz_minute,
EXTRACT(timezone_region FROM SYSTIMESTAMP) tz_reg,
EXTRACT(timezone_abbr FROM SYSTIMESTAMP) tz_abbr
FROM DUAL;

To add seconds to a date value

SELECT TO_CHAR(sysdate ,'DD-MON-YYYY HH24:MI:SS') , TO_CHAR(sysdate + INTERVAL '60' SECOND,'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

To add minutes to a date value

SELECT TO_CHAR(sysdate ,'DD-MON-YYYY HH24:MI:SS') , TO_CHAR(sysdate + INTERVAL '5' MINUTE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

To add 5 Hours to a date value

SELECT TO_CHAR(sysdate ,'DD-MON-YYYY HH24:MI:SS') , TO_CHAR(sysdate + INTERVAL '5' HOUR,'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

To subtract 1 day, 2 minutes and 2 seconds from a date value

SELECT systimestamp, systimestamp - INTERVAL '1 00:02:02' DAY TO SECOND FROM DUAL;

To cast a Date "Datatype" as TIMESTAMP "Datatype"

SELECT SYSDATE, CAST(SYSDATE AS TIMESTAMP) FROM DUAL;

To add Months to a Date using ADD_MONTHS and INTERVAL

SELECT SYSDATE, ADD_MONTHS(SYSDATE, 6), SYSDATE + INTERVAL '6' MONTH FROM DUAL;

To get the time difference between 2 timezones

Example - To get time interval between India (Timezone "Asia/Kolkata") and US (Timezone "US/Eastern")

SELECT CAST(systimestamp AT TIME ZONE 'Asia/Kolkata' as timestamp) -  CAST(systimestamp AT TIME ZONE 'US/Eastern' as TIMESTAMP) FROM dual;

To get a list of all Timezones

select * from v$timezone_names;

To set the timezone at session level

ALTER SESSION SET time_zone = local;

To check if year is a leap year 

The logic applied is IF divisible by 4 - then it is leap year. If divisible by 100 then not a leap year unless divisible by 400

create or replace FUNCTION is_leap_year (p_n_year IN NUMBER)
  RETURN BOOLEAN
IS
  p_b_leap_year   BOOLEAN := FALSE;
BEGIN
  IF     (MOD (p_n_year, 4) = 0)
     AND ((MOD (p_n_year, 100) <> 0) OR (MOD (p_n_year, 400) = 0))
  THEN
     p_b_leap_year := TRUE;
  END IF;

  RETURN p_b_leap_year;
END;

To Find out the number of days in a month Pseudo Logic 

If the month is Jan(1), Mar(3), May(5), July(7), Aug(8), Oct(10), Dec(12) - no of days = 31 

If the month is Apr(4), Jun(6), Sep(9), Nov(11) - no of days = 30 

If the month is Feb, check if it is a leap year. If so no of days = 29. Else no of days = 28 

There is already a good function for this in 

Reference - http://www.orafaq.com/wiki/PL/SQL_Calendar 

So instead of reinventing the wheel I am just copying the same code here, for an easy reference.

FUNCTION days_in_a_month (p_n_month IN NUMBER, p_n_year IN NUMBER)
  RETURN NUMBER
IS
  days_in_month   NUMBER (2);
BEGIN
  CASE
     WHEN (   p_n_month = 1
           OR p_n_month = 3
           OR p_n_month = 5
           OR p_n_month = 7
           OR p_n_month = 8
           OR p_n_month = 10
           OR p_n_month = 12
          )
     THEN
        days_in_month := 31;
     WHEN p_n_month = 2
     THEN
        IF (is_leap_year (p_n_year))
        THEN
           days_in_month := 29;
        ELSE
           days_in_month := 28;
        END IF;
     ELSE
        days_in_month := 30;
  END CASE;

  RETURN days_in_month;
END;

To get the date the Last Friday of the Month

SELECT NEXT_DAY(LAST_DAY(TO_DATE('23-MAY-2016','DD-MON-YYYY')) - 7,'FRIDAY') FROM DUAL;

To get the day for the Last Day of the Month

SELECT TO_CHAR(LAST_DAY(SYSDATE),'DAY')  FROM DUAL; 

Exercise 

To get time difference in milliseconds from 2 timestamp variables

 

Quote for the day 

You Learn More From Failure Than From Success. Don’t Let It Stop You. Failure Builds Character.

-- Unknown 

References

http://www.orafaq.com/wiki/PL/SQL_Calendar