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.
