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.
Ur's
AmarAlam
0 comments:
Post a Comment