How to work with Date Functions?? How to add days in current date?

How to work with Date Functions??

How to add days in current date?

 select sysdate + 3 from dual;

----------------------------------------------------

 select '07-dec-04' + 3 from dual; -- Gives error

select to_date('07-dec-04')  + 3 from dual;

select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value

---------------------------------------------------------------

date + number/24 --> Adds a number of hours to a date

Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.

select sysdate + 4/24 from dual;

---------------------------------------------------

How to do Months_between(d1,d2)

If d1 > d2 then +ve else -ve

Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;

select months_between(sysdate,hiredate) from emp

select round(months_between(sysdate,hiredate),0) As "Months Between" from emp

 Assignment - Find the years between using months_between

select months_between('13-dec-04','24-jul-04') from dual

select round(months_between('13-dec-04','24-jul-04'),0) from dual

Add_months(date,n) [n cand be +ve or -ve]

select add_months('4-dec-04',3) from dual; Gives 04-Mar-05

select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04

-----------------------------------------------------------------------------------------------

Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.

select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04

OR

Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2  ...Saturday as 7

select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04

 Note: The number cannot be negative.

----------------------------------------------------------------------

Last_Day(date) - Returns the last date of the month.

select last_day(sysdate) from dual;

--------------------------------------------------------------------------

Round(Date,fmt)

If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.

E.g select round(to_date('04-dec-04'),'Month') from dual  ----> 01-DEC-04

select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04

select round(to_date('16-dec-04'),'Month') from dual; ---->  01-JAN-05

select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04

select round(to_date('30-Jun-04'), 'Year') from dual;  --> 01-JAN-04

 select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05

Trunc

select trunc(to_date('23-dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('07-Dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('23-Dec-04'),'Year') from dual;    Gives 01-Jan-04

select trunc(to_date('02-Feb-04'),'Year') from dual;     Gives 01-Jan-04

To_Char

To see all the records of employees joined after 1982

select * from emp

where to_char(hiredate,'yy') >= 82;

Or  where to_char(hiredate,'yyyy’) >= 1982

To see all the records of employees who have joined from October of any month.

select * from emp

where to_char(hiredate,'mm') >= 10; 

(Note à Only number 1 to 12 can be used for comparison)

To see all the records of employees who have joined from 21st date.

select * from emp

where to_char(hiredate,'dd') >= 21;

To see all the records of employees who have joined from April-81

select * from emp

where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81

To see all the records of employees who have joined after 28-Sep-81

select * from emp

where hiredate >  '28-Sep-81'

DATE FORMAT MODEL

YEARS

select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;

MONTHS

select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')

 || '-' || TO_CHAR(hiredate,'MON') FROM EMP;

DAYS

select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')

 || '-' || TO_CHAR(hiredate,'DY') FROM EMP;

ADVANCED FORMATS -

CENTURY

SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;

select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"

FROM EMP;

SYEAR  The spelled out year.

select to_char(HIREDATE,'SYEAR') "YEAR"

FROM EMP

Q  To get the quarter of the year (1,2,3 and 4)

select to_char(HIREDATE,'Q') "Quarter"

FROM EMP;

 RM   Roman numeral month

select to_char(HIREDATE,'RM')"MONTH"

FROM EMP;

J Julian day - The number of days since 31Dec 4713 B.C.

select to_char(HIREDATE,'J')"JulianDay"

FROM EMP;

TIME

select to_char(sysdate,'hh:mi:ss') FROM DUAL;

To show AM or PM

select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;

SSSS Seconds past midnight (0-86399)  [ (60secs * 60 min * 24) - 1]

select to_char(sysdate,'ssss') from dual;

Suffixes -

select to_char(sysdate,'ddth') from dual;

select to_char(sysdate,'ddsp') from dual;

select to_char(sysdate,'ddspth') from dual;

 RR DATE FORMAT -

create table rryy

(name varchar2(10),

 dob date);

NAME

DOB

A

24-SEP-02

B

12-DEC-67

C

14-JAN-59

D

05-APR-04

  A AND D ARE IN 2000 CENTURY.

  B AND C ARE IN 1900 CENTURY.

To see the records who have dob of 19th century.

Suppose yy format is used -

SELECT * FROM RRYY

WHERE TO_CHAR(DOB,'YY') < 99.

It assumes all the years are in the 20th  century (2099). So all 4 records will come.

So to show only ,B and C records  --

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90',  'DD-MON-RR');

Here 90 it understands as 1990.

If  YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');

Here 90 it understands as 2090. So again all 4 records will come.

Similarly ---

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-RR');

Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.

A and D records will come.

If YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-YY');

Due to YY it takes 90 as 2090. It will not find any record above 2090.