14. DATE FUNCTIONS

DATE FUNCTIONS

DATE FORMATS

D(1,2,..) ------------------ DAY OF THE WEEK(1-7)

DD(1 TO 31) ------------- DAY OF THE MONTH(1-31)

DDD(1 TO 365) ---------- DAY OF THE YEAR

DY(SUN,MON,..) --------- FIRST THREE CHARACTERS OF THE WEEK

Dy

DAY(SUNDAY,…) -------- COMPLETE CHARACTERS OF THE WEEK

Day

MM(1-12) --------------- MONTH OF THE YEAR

MON(JAN,FEB,..) ------- FIRST THREE CHARACTERS OF THE MONTH

MONTH(JANUARY,..) --- COMPLETE CHARACTERS OF THE MONTH

Month

Y(3)

YY(13) ------------------ LAST ONE,TWO,THREE DIGITS OF THE YEAR

YYY(013)

YYYY(2013) ------------ COMPLETE DIGITS OF THE YEAR

SYYYY (AD AND BC) --- YEAR WITH SIGN FOR BC –VE AND +VE FOR AD

RR(13) ------------------ LAST TWO DIGITS OF THE YEAR

RRRR(2013) ------------- YEAR COMPLETE DIGITS

I

IY

IYY

IYYY --------------  DIGITS OF THE YEAR IN ISO FORMATS

W(1-5) -----------  WEEK OF THE MONTH

WW(1-52) -------  WEEK OF THE YEAR

IW ----------------  WEEK OF THE YEAR IN ISO STANDARDS

FM ----------------  FILL MODE( ELEMENATES SPACES AND ZEROES)

FF ----------------- FRACTION OF SECONDS

XF ----------------- EXACT FORMAT

HH ---------------- HOUR FORMAT(DEFAULT 12 HOUR)

HH12 ------------- 12 HOUR FORMAT

HH24 ------------- 24 HOUR FORMAT

MI ----------------- MINUTES

SS ----------------- SECONDS

SP ----------------- TO SPELL THE DIGITS

TH ----------------- ORDINALS(TH,RD,ST,..)

Q ------------------ QUARTER OF THE YEAR

J ------------------- JULIAN DAY(A/C TO JULIAN CALENDRE)

RM ----------------- ROMAN NUMBERICAL LETTERS WHICH REPRESENTS NO OF……… MONTHS

DL ----------------- LONG DATE

DS ----------------- SHORT DATE

TZH --------------- TIMEZONE HOUR

TZM --------------- TIMEZONE MINUTE

TZR --------------- TIMEZONE REGION

TZA --------------- TIMEZONE ABBAR

AM/PM 

. Period

:                

-

 /

“TEXT”

DATE FUNCTIONS

• SYSDATE
• CURRENT_DATE
•  SYSTIMESTAMP
•  CURRENT_TIMESTAMP
•  LOCAL TIMESTAMP
•  DBTIMEZONE
•  ADD_MONTHS
• MONTHS_BETWEEN
• NEXT_DAY
•  LAST_DAY
•  EXTRACT
•  ROUND
• TRUNC
• NEW_TIME

EG: SELECT SYSDATE,CURRENT_DATE FROM DUAL;

      SELECT SYSTIMESTAMP,CURRENT_TIMESTAMP FROM DUAL;

      SELECT LOCALTIMESTAMP FROM DUAL;

         

      SELECT DBTIMEZONE FROM DUAL;

    ADD_MONTHS: TO ADD OR SUBSTRACT NUMBER OF MONTHS TO A GIVEN DATE;

                    SYN: ADD_MONTHS (DATE, N);

EG:  SELECT SYSDATE,ADD_MONTHS(SYSDATE,1),SYSDATE+30 FROM DUAL;

       SELECT SYSDATE,ADD_MONTHS (SYSDATE,-1) FROM DUAL;

 MONTHS_BEWTWEEN: To display the no of months between two given date but always date1>date2

             SYN: MONTHS_BETWEEN (DATE1, DATE2);

 EG:   SELECT SYSDATE, HIREDATE, MONTHS_BETWEEN (SYSDATE, HIREDATE)

         FROM EMP;

        SELECT SYSDATE, HIREDATE, ROUND(MONTHS_BETWEEN (SYSDATE,                HIREDATE)) FROM EMP;

  NEXT_DAY: Based on the format it display the next day of the week

                       SYN: NEXT_DAY (DATE,’FORMAT’);

EG: SELECT NEXT_DAY (SYSDATE,’MON’) FROM DUAL;

      SELECT NEXT-DAY (SYSDATE,’TUE’) FROM DUAL;

LAST_DAY: Based on the given date.it displays the last day date of the month

                           

                           SYN: LAST_DAY (DATE);

EG: SELECT LAST-DAY (SYSDATE) FROM DUAL;

EXTRACT:

SYN: EXTRACT (YEAR|MONTH|DAY|HOUR|MINUTE|SECOND FROM DATE);

EG: SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

      SELECT EXTRACT (MONTH FROM SYSDATE) FROM DUAL;

      

      SELECT EXTRACT ( DAY FROM SYSDATE) FROM DUAL;

      SELECT EXTRACT (HOUR FROM SYSTIMESTAMP) FROM DUAL;

      SELECT EXTRACT (MINUTE FROM SYSTIMESTAMP) FROM DUAL;

      SELECT EXTRACT (SECONDS FROM SYSTIMESTAMP) FROM DUAL;

    ROUND: Rounds the date based on formats

     SYN: ROUND (DATE,’YEAR|MONTH|DAY’);

EG:  SELECT ROUND (SYSDATE,’YEAR’) FROM DUAL;

       SELECT ROUND (SYSDATE,’MONTH’) FROM DUAL;

      

       SELECT ROUND (SYSDATE,’DAY’) FROM DUAL;

TRUNC: TRUNC THE DATES

SYN: TRUNC (DATE,’FORMAT’);

EX: SELECT TRUNC (SYSDATE,’YEAR’) FROM DUAL;

      SELECT TRUNC (SYSDATE,’MONTH’) FROM DUAL;