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

References

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

 

Quote for the day 

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

-- Unknown 

God's Word for the day

Will you not leave the fruit of your labours to another, 

  and what you acquired by toil to be divided by lot? 

Give, and take, and indulge yourself, 

  because in Hades one cannot look for luxury.

Sirach 14:15-16

Do Not Worry 

And why do you worry about clothing? 

  Consider the lilies of the field how they grow;

They neither toil nor spin, yet I tell you,

  even Solomon in all his glory was not clothed like one of these.

Mathew 6:28-29