NUMBER FUNCTIONS
1)
ABS(n) -- n-- stands for input number
It
returns the absolute value of the number.
It means it will always give the positive
value though
It
is a negative or positive.
Ex:
select abs(-100) from dual;
o/p
-- 100
select abs(100) from dual;
o/p
-- 100
select abs(0) from dual;
o/p
-- 100
-------------------------------------------------------------------------------------
2)Sqrt(n) -- It returns the square root of the value
select sqrt(4) from dual;
o/p -- 2
------------------------------------------------------------------------------------
3)Power(m,n) -- It gives the o/p as m power of n..
EX:
select power(4,2) from dual;
o/p -- 16
------------------------------------------------------------------------------------
4)MOD(m,n) – It returns the reminder....
select mod(11,3) from dual;
o/p --2
select mod(12,3) from dual;
o/p --- 0
------------------------------------------------------------------------------------
5)SIGN(n) – n stands for NUMBER
If
the number is positive it return 1
If
the number is negative it return -1
If
the number is 0 then it returns 0
Ex:
select sign(-89) from dual;
o/p
-- -1
select sign(123) from dual
o/p
== 1
select sign(0) from dual;
o/p == 0
6)Trunc --This will trunc the
value
EX:
select trunc(17.6789) from dual;
o/p -- 17
select trunc(17.6789,1) from dual;
o/p -- 17.6
select trunc(17.6789,2) from dual;
o/p -- 17.67
select trunc(17.6789,3) from dual;
o/p -- 17.678
--------------------------------------------------------
7)ROUND -- this will round the given value
select round(17.6789) from dual;
o/p -- 18
select trunc(17.4321) from dual;
o/p --17
select round(17.6789,2) from dual;
o/p -- 17.68
select round(17.4321,2) from dual;
o/p -- 17.43
------------------------------------------------------------------
8)Ceil -- This will always gives the upper value
EX:
select ceil(17.6789) from dual;
o/p -- 18
select ceil(17.4321) from dual;
o/p -- 18
------------------------------------------------------------------
9)floor -- this will always gives the lower value
EX:
select floor(17.6789) from dual;
o/p -- 17
select floor(17.4321) from dual;
o/ p –17
NOTE:
Make
sure your are perfect with the difference between round,trunc,ceil,floor(Very
Important for Interview)
Make sure your are perfect with the difference between
SIGN,ABS(Very Important for Interview)
CHARACTER FUNCTIONS
1)Length-- Returns the length of the string.
EX:
select ename,length(ename)
from emp;
2)ASCII-- Returns the ascii value of the string
Ex:
select ename,ascii('a') from emp;
3)Chr-- Returns the char value
Ex:
select ename,chr(97) from emp;
Note: In real time most of the times they
use chr(10) which means go to next line.
4)Concat--using this function we can concat two values.Also we can use
pipe(||) symbol
EX:
select concat(ename,empno) from emp;
select ename||'----'||empno from emp;
select
ename||chr(10)||empno||'----'||sal from
emp;
5)Lower-- It will convert the string in to lower values
EX:
select job,lower(job) from emp;
Display all emp details whose job is
salesman
-------
Select *
from emp
where lower(job) = 'salesman';
6)Upper—It will convert the values into upper case
EX:
select upper(job),job from emp;
Display emp details whose job is manager
EX:
select *
from emp
where upper(job) = 'MANAGER';
7)Initcap -- It will convert the values into Initcap case
EX:
select inticap(job),job from emp;
8)Reverse --This will reverse the string
EX:
select ename,reverse(ename) from emp;
9)LPAD(S,N,C) -- Using this function we
can left pad some values to the string
s --
String
n
-- Number
c --
Character
EX:
select ename,lpad(ename,10,'*') from emp;
select ename,lpad(ename,10,'*'),length
(ename),length(lpad(ename,10,'*'))
from emp;
10)RPAD(S,N,C)
-- Using this function we
can right pad some values to the string
s --
String
n
-- Number
c --
Character
EX:
select ename,rpad(ename,8,'*') from emp;
select
ename,rpad(ename,8,'*'),length(ename),
length(rpad(ename,8,'*'))
from emp;
11)Replace(s,c1,c2)
-- Using this function we can replace the value
s -- String
c1 --Character1 which needs to be replaced
c2 --Character2 which is being replaced by
EX:
select ename,replace(ename,'A','xyz') from
emp;
select ename,replace(ename,'AR','xyz')
from emp;
12)TRANSLATE(s,c1,c2) -- This will translate character to character by comparing the c1
and c2
s -- String
c1 --Character1 which needs to be replaced
c2 --Character2 which is being replaced by
EX:
select ename ,replace(ename,'A','xyz'),translate
(ename,'a','xyz') from emp;
select ename ,replace(ename,'A','xyz'),translate
(ename,'A','xyz') from emp;
select ename
,replace(ename,'AR','xyz'),translate
(ename,'ARG','xyz') from emp;
select ename
,replace(ename,'AR','xyz'),translate
(ename,'ATR','xyz') from emp;
In the above example
Using
Replace Command the AR string will be replaced by xyz
Using Translate Command A will be translated to x
T will be translated to y
Z
will be translated to Z
select translate('raghu','esh','xyz') from
dual;
o/p -- ragzu
select
translate('rameshpower','esh','xyz') from dual;
o/p -- ramxyzpowxr
13)SIGN(N) --
If negative then it returns -1.
If positive it returns 1
If 0 it returns 0
EX:
select sign(-12346) from dual;
o/p
-1
select sign(123456) from dual;
o/p
1
select sign(0) from dual;
o/p
0
14)LTRIM(string,character) – It trims the given character from left side if exists
EX:
select ltrim('SrSSSSSRameSh','S') from
dual;
o/p rSSSSSRameSh
select ltrim('SSSSSSRameSh','S') from
dual;
O/P
RameSh
As per above examples it will trim all S
from the left side
15)RTRIM(string,character) – It trims the given character from right side if exists
EX:
select rtrim('SSSSSSRameshSSSSSS','S')
from dual;
O/P SSSSSSRamesh
--------------------------------------
Note:
These ltrim and rtirm functions are mainly
used to trim the values..
Speacially in real time when we are
inserting the data in to the custom table,if the data contains any empty spaces
at left or right side then we trim the.please check the below example...
select rtrim(' Checking
') from dual;
select ltrim(' Checking
') from dual;
select ltrim(rtrim(' Checking
')) from dual;
-------------------------------------
16)
SUBSTR(s,m,n)--
Using this function we can cut
any part of the string.
S -- String
M
-- Starting position
N
-- Ending position
EX:
select substr('SARASWATHY',5) from dual
o/p --
SWATHY
select substr('SARASWATHY',5,2) from dual
o/p -- SWA
select substr('SARASWATHY',5,4) from dual
o.p -- SWAT
select ename,substr(ename,2,4) from emp
--------------------------------------------------------
17)
INSTR(s,c,m,n) -- This returns the position of the
character searched for in the string
by taking the starting position and occurrence
of character in the string.
S -- String
C -- Character
M --Starting position
N -- Occurence
Note: This is
the character function which returns number....
EX:
select instr('SARASWATHY','A',1,1) FROM
DUAL;
o/p –2
select instr('SARASWATHY','A',1,3) FROM
DUAL;
o/p --7
select instr('SARASWATHY','A',2,1) FROM
DUAL;
o/p --2
select instr('SARASWATHY','A',2,3) FROM
DUAL;
o/p –7
NOTE:
Make
sure your are perfect with the difference between replace,transalate(Very
Important for Interview)
Make sure your are perfect with the difference between Instr,substr(Very
Important for Interview)
Make sure you are very perfect with the syntax and each function
usage(very Important for Interview).
DATE FUNCTIONS....
SYSDATE: -- this is an oracle keyword
which will always retrun current
date and time
select sysdate from dual;
Inorder to remove the time when printing
the system date without time
we can use the trunc command;
select trunc(sysdate) from dual
1)
ADD_MONTHS(DATE,NUMBER)
This will add the months for the given
date...
select sysdate,add_months(sysdate,2) from
dual;
it returns the date added by 2 months
select sysdate,add_months(sysdate,-2) from
dual;
it returns the date substracted by 2
months
2)MONTHS_BETWEEN(DATE1,DATE2)
This will give the difference of months
between two dates...
select sysdate,sysdate+2,sysdate-2 from
dual
Select add_months(trunc(sysdate),12) add_12_months
,trunc(sysdate)
system_date
,trunc(sysdate)-2 system_date_minus
,months_between(trunc(sysdate),trunc(sysdate)-65)
months_diff
from dual
3)NEXT_DAY(DATE,DAY)
This will give us the next day of the
given date...
select next_day(trunc(sysdate),'MONDAY') from
dual;
4)LAST_DAY(DATE)
This will give us the last date of the
month...
select last_day(sysdate) from dual
SYSDATE :- This is the system current date
which is an oracle seeded term...
EX:
Select sysdate from dual;
o/p : This will provide the date of the
year on which the above Query as Ran...
select add_months(hiredate,2)
,hiredate
,next_day(hiredate,'SUNDAY')
,last_day(hiredate)
from emp
-------------------------------------------------------------
Ex1:
fetch the 1date of the next month as per given date parameter...
select last_day(trunc(sysdate))+1 from dual;
select hiredate
,last_day(hiredate)+1
from emp
Ex2: Fetch the 1 date of that month as per
given date parameter....
select
last_day(add_months(trunc(sysdate),-1))+1 from dual;
Ex3: Fetch the last date of the previous
months as per given date parameter...
select
last_day(add_months(trunc(sysdate),-1)) from dual;
Ex4:
fetch the 15 of the moth as per given date parameter
select
(last_day(add_months(trunc(sysdate),-1))+1) +14 from dual;
-------------------------------------------------------------
TRUNC --
This can be used to remove the time from
the date and only display date....
select trunc(sysdate),sysdate
,trunc(17.639,2),round(17.639,2)from dual;
Ex1: Return first date of the year
Select Trunc(sysdate,'YEAR') from dual;
Ex2: Retun First date of the Quater
Select Trunc(sysdate,'Q') from dual;
Ex3: Return First date of the month
Select Trunc(sysdate,'MONTH') from dual;
Ex4: Return First date of the week
Select Trunc(sysdate,'DAY') from dual;
Ex5: Retun last date of the previous
year...
Select Trunc(sysdate,'YEAR')-1 from dual;
Ex6: first date of the next year
Select
add_months(Trunc(sysdate,'YEAR'),12) from dual;
--------------------------------------------------------------
TO_CHAR:
syntax:
to_char(date_column_name,format)
This function is used to convert any other
data type in to character...
Note: We cannot use to_char function to
change the character functions again.
Speacially in most cases of real time
we
use this to change the date datatype column format to any required
formats......
ex1: Select
to_char(trunc(sysdate),'DD-MON-YYYY') from dual;
Select to_char(trunc(sysdate),'DD-MON-YYYY
HH24:mi:ss') from dual;
Select to_char(trunc(sysdate),'DD-MON-YYYY
HH:mi:ss') from dual;
Ex2: select to_char(trunc(sysdate),'DD') from dual;
Ex3: select to_char(trunc(sysdate),'MON') from dual;
Ex4: select to_char(trunc(sysdate),'YYYY') from dual;
Ex5: select to_char(trunc(sysdate),'yyyy/mm/dd') from
dual;
Ex6: select to_char(trunc(sysdate),'mon-yy') from dual;
----------------------------
Select sysdate
,to_char(trunc(sysdate),'DD-MON-YYYY') a
,to_char(trunc(sysdate),'DD') b
,to_char(trunc(sysdate),'MON') c
,to_char(trunc(sysdate),'YYYY') d
,to_char(trunc(sysdate),'yyyy/mm/dd') e
,to_char(trunc(sysdate),'DD/MON-YYYY') f
,to_char(trunc(sysdate),'mon-yy') g
from dual;
-----------------------------
Ex7: to retreive no of the week of the
date of that year
select
to_char(trunc(sysdate),'WW') from dual;
select
'WW'||to_char(trunc(sysdate),'WW')||'--'||to_char(sysdate,'YYYY') from
dual;
--------------------
Select hiredate
,to_char(trunc(hiredate),'DD-MON-YYYY') a
,to_char(trunc(hiredate),'DD') b
,to_char(trunc(hiredate),'MON') c
,to_char(trunc(hiredate),'YYYY') d
,to_char(trunc(hiredate),'yyyy/mm/dd') e
,to_char(trunc(hiredate),'DD/MON-YYYY') f
,to_char(trunc(hiredate),'mon-yy') g
, to_char(trunc(hiredate),'WW') h
from emp;
-----------------------------------
Print the Month and week of that date
Ex8 :
select
to_char(trunc(sysdate),'MON') Month_1
,to_char(trunc(sysdate),'WW') week_no
,to_char(trunc(sysdate),'MON')||'&'||to_char(trunc(sysdate),'WW')
from dual;
o/p ===
JUN-23
Ex9 : retreive the day of the year
select
to_char(trunc(sysdate),'DAY') from dual;
Last day of the Year
-------------------------------
select
to_char(add_months(trunc(sysdate,'year'),12)-1,'DAY') from dual
TO_DATE
This function is used to convert character
date type into Date ..
SYNTAX
---------
to_date(columns name,format)
Note: We cannot use to_date function on
the date columns.
ex: select to_date(hiredate,'YYYY-MON-DD')
from emp
the above sql stmt will error out as we
cannot apply to_date on a date datatype
Speacially in most cases of real time we
use this
to
change the date datatype column format to any required formats......
I want to change the format of hiredate
colum value as dd-mon-yyyy
and it should be the date type as date
only
first change the date column to char by
using to_char to reqired format
and then apply to_date to make it date data
type
select
to_date(to_char(hiredate,'YYYY-MON-DD'),'YYYY-MON-DD') from emp
--------------------
Practice:
Last date of this month
First date of the next month
Last date of the last month
First date of this month
15 date of this month
First date of this year.
First date of the next year
last date of this year
last day of this year. -- ?
first date of this quater
first date of this week....
----
First friday of next month
First friday of ths month
last friday of this month
-----
select last_day(trunc(sysdate)) from dual;
select last_day(trunc(sysdate))+1 from
dual;
select trunc(sysdate,'MONTH')-1 NUMBER FUNCTIONS
1)
ABS(n) -- n-- stands for input number
It
returns the absolute value of the number.
It means it will always give the positive
value though
It
is a negative or positive.
Ex:
select abs(-100) from dual;
o/p
-- 100
select abs(100) from dual;
o/p
-- 100
select abs(0) from dual;
o/p
-- 100
-------------------------------------------------------------------------------------
2)Sqrt(n) -- It returns the square root of the value
select sqrt(4) from dual;
o/p -- 2
------------------------------------------------------------------------------------
3)Power(m,n) -- It gives the o/p as m power of n..
EX:
select power(4,2) from dual;
o/p -- 16
------------------------------------------------------------------------------------
4)MOD(m,n) – It returns the reminder....
select mod(11,3) from dual;
o/p --2
select mod(12,3) from dual;
o/p --- 0
------------------------------------------------------------------------------------
5)SIGN(n) – n stands for NUMBER
If
the number is positive it return 1
If
the number is negative it return -1
If
the number is 0 then it returns 0
Ex:
select sign(-89) from dual;
o/p
-- -1
select sign(123) from dual
o/p
== 1
select sign(0) from dual;
o/p == 0
6)Trunc --This will trunc the
value
EX:
select trunc(17.6789) from dual;
o/p -- 17
select trunc(17.6789,1) from dual;
o/p -- 17.6
select trunc(17.6789,2) from dual;
o/p -- 17.67
select trunc(17.6789,3) from dual;
o/p -- 17.678
--------------------------------------------------------
7)ROUND -- this will round the given value
select round(17.6789) from dual;
o/p -- 18
select trunc(17.4321) from dual;
o/p --17
select round(17.6789,2) from dual;
o/p -- 17.68
select round(17.4321,2) from dual;
o/p -- 17.43
------------------------------------------------------------------
8)Ceil -- This will always gives the upper value
EX:
select ceil(17.6789) from dual;
o/p -- 18
select ceil(17.4321) from dual;
o/p -- 18
------------------------------------------------------------------
9)floor -- this will always gives the lower value
EX:
select floor(17.6789) from dual;
o/p -- 17
select floor(17.4321) from dual;
o/ p –17
NOTE:
Make
sure your are perfect with the difference between round,trunc,ceil,floor(Very
Important for Interview)
Make sure your are perfect with the difference between
SIGN,ABS(Very Important for Interview)
CHARACTER FUNCTIONS
1)Length-- Returns the length of the string.
EX:
select ename,length(ename)
from emp;
2)ASCII-- Returns the ascii value of the string
Ex:
select ename,ascii('a') from emp;
3)Chr-- Returns the char value
Ex:
select ename,chr(97) from emp;
Note: In real time most of the times they
use chr(10) which means go to next line.
4)Concat--using this function we can concat two values.Also we can use
pipe(||) symbol
EX:
select concat(ename,empno) from emp;
select ename||'----'||empno from emp;
select
ename||chr(10)||empno||'----'||sal from
emp;
5)Lower-- It will convert the string in to lower values
EX:
select job,lower(job) from emp;
Display all emp details whose job is
salesman
-------
Select *
from emp
where lower(job) = 'salesman';
6)Upper—It will convert the values into upper case
EX:
select upper(job),job from emp;
Display emp details whose job is manager
EX:
select *
from emp
where upper(job) = 'MANAGER';
7)Initcap -- It will convert the values into Initcap case
EX:
select inticap(job),job from emp;
8)Reverse --This will reverse the string
EX:
select ename,reverse(ename) from emp;
9)LPAD(S,N,C) -- Using this function we
can left pad some values to the string
s --
String
n
-- Number
c --
Character
EX:
select ename,lpad(ename,10,'*') from emp;
select ename,lpad(ename,10,'*'),length
(ename),length(lpad(ename,10,'*'))
from emp;
10)RPAD(S,N,C)
-- Using this function we
can right pad some values to the string
s --
String
n
-- Number
c --
Character
EX:
select ename,rpad(ename,8,'*') from emp;
select
ename,rpad(ename,8,'*'),length(ename),
length(rpad(ename,8,'*'))
from emp;
11)Replace(s,c1,c2)
-- Using this function we can replace the value
s -- String
c1 --Character1 which needs to be replaced
c2 --Character2 which is being replaced by
EX:
select ename,replace(ename,'A','xyz') from
emp;
select ename,replace(ename,'AR','xyz')
from emp;
12)TRANSLATE(s,c1,c2) -- This will translate character to character by comparing the c1
and c2
s -- String
c1 --Character1 which needs to be replaced
c2 --Character2 which is being replaced by
EX:
select ename ,replace(ename,'A','xyz'),translate
(ename,'a','xyz') from emp;
select ename ,replace(ename,'A','xyz'),translate
(ename,'A','xyz') from emp;
select ename
,replace(ename,'AR','xyz'),translate
(ename,'ARG','xyz') from emp;
select ename
,replace(ename,'AR','xyz'),translate
(ename,'ATR','xyz') from emp;
In the above example
Using
Replace Command the AR string will be replaced by xyz
Using Translate Command A will be translated to x
T will be translated to y
Z
will be translated to Z
select translate('raghu','esh','xyz') from
dual;
o/p -- ragzu
select
translate('rameshpower','esh','xyz') from dual;
o/p -- ramxyzpowxr
13)SIGN(N) --
If negative then it returns -1.
If positive it returns 1
If 0 it returns 0
EX:
select sign(-12346) from dual;
o/p
-1
select sign(123456) from dual;
o/p
1
select sign(0) from dual;
o/p
0
14)LTRIM(string,character) – It trims the given character from left side if exists
EX:
select ltrim('SrSSSSSRameSh','S') from
dual;
o/p rSSSSSRameSh
select ltrim('SSSSSSRameSh','S') from
dual;
O/P
RameSh
As per above examples it will trim all S
from the left side
15)RTRIM(string,character) – It trims the given character from right side if exists
EX:
select rtrim('SSSSSSRameshSSSSSS','S')
from dual;
O/P SSSSSSRamesh
--------------------------------------
Note:
These ltrim and rtirm functions are mainly
used to trim the values..
Speacially in real time when we are
inserting the data in to the custom table,if the data contains any empty spaces
at left or right side then we trim the.please check the below example...
select rtrim(' Checking
') from dual;
select ltrim(' Checking
') from dual;
select ltrim(rtrim(' Checking
')) from dual;
-------------------------------------
16)
SUBSTR(s,m,n)--
Using this function we can cut
any part of the string.
S -- String
M
-- Starting position
N
-- Ending position
EX:
select substr('SARASWATHY',5) from dual
o/p --
SWATHY
select substr('SARASWATHY',5,2) from dual
o/p -- SWA
select substr('SARASWATHY',5,4) from dual
o.p -- SWAT
select ename,substr(ename,2,4) from emp
--------------------------------------------------------
17)
INSTR(s,c,m,n) -- This returns the position of the
character searched for in the string
by taking the starting position and occurrence
of character in the string.
S -- String
C -- Character
M --Starting position
N -- Occurence
Note: This is
the character function which returns number....
EX:
select instr('SARASWATHY','A',1,1) FROM
DUAL;
o/p –2
select instr('SARASWATHY','A',1,3) FROM
DUAL;
o/p --7
select instr('SARASWATHY','A',2,1) FROM
DUAL;
o/p --2
select instr('SARASWATHY','A',2,3) FROM
DUAL;
o/p –7
NOTE:
Make
sure your are perfect with the difference between replace,transalate(Very
Important for Interview)
Make sure your are perfect with the difference between Instr,substr(Very
Important for Interview)
Make sure you are very perfect with the syntax and each function
usage(very Important for Interview).
DATE FUNCTIONS....
SYSDATE: -- this is an oracle keyword
which will always retrun current
date and time
select sysdate from dual;
Inorder to remove the time when printing
the system date without time
we can use the trunc command;
select trunc(sysdate) from dual
1)
ADD_MONTHS(DATE,NUMBER)
This will add the months for the given
date...
select sysdate,add_months(sysdate,2) from
dual;
it returns the date added by 2 months
select sysdate,add_months(sysdate,-2) from
dual;
it returns the date substracted by 2
months
2)MONTHS_BETWEEN(DATE1,DATE2)
This will give the difference of months
between two dates...
select sysdate,sysdate+2,sysdate-2 from
dual
Select add_months(trunc(sysdate),12) add_12_months
,trunc(sysdate)
system_date
,trunc(sysdate)-2 system_date_minus
,months_between(trunc(sysdate),trunc(sysdate)-65)
months_diff
from dual
3)NEXT_DAY(DATE,DAY)
This will give us the next day of the
given date...
select next_day(trunc(sysdate),'MONDAY') from
dual;
4)LAST_DAY(DATE)
This will give us the last date of the
month...
select last_day(sysdate) from dual
SYSDATE :- This is the system current date
which is an oracle seeded term...
EX:
Select sysdate from dual;
o/p : This will provide the date of the
year on which the above Query as Ran...
select add_months(hiredate,2)
,hiredate
,next_day(hiredate,'SUNDAY')
,last_day(hiredate)
from emp
-------------------------------------------------------------
Ex1:
fetch the 1date of the next month as per given date parameter...
select last_day(trunc(sysdate))+1 from dual;
select hiredate
,last_day(hiredate)+1
from emp
Ex2: Fetch the 1 date of that month as per
given date parameter....
select
last_day(add_months(trunc(sysdate),-1))+1 from dual;
Ex3: Fetch the last date of the previous
months as per given date parameter...
select
last_day(add_months(trunc(sysdate),-1)) from dual;
Ex4:
fetch the 15 of the moth as per given date parameter
select
(last_day(add_months(trunc(sysdate),-1))+1) +14 from dual;
-------------------------------------------------------------
TRUNC --
This can be used to remove the time from
the date and only display date....
select trunc(sysdate),sysdate
,trunc(17.639,2),round(17.639,2)from dual;
Ex1: Return first date of the year
Select Trunc(sysdate,'YEAR') from dual;
Ex2: Retun First date of the Quater
Select Trunc(sysdate,'Q') from dual;
Ex3: Return First date of the month
Select Trunc(sysdate,'MONTH') from dual;
Ex4: Return First date of the week
Select Trunc(sysdate,'DAY') from dual;
Ex5: Retun last date of the previous
year...
Select Trunc(sysdate,'YEAR')-1 from dual;
Ex6: first date of the next year
Select
add_months(Trunc(sysdate,'YEAR'),12) from dual;
--------------------------------------------------------------
TO_CHAR:
syntax:
to_char(date_column_name,format)
This function is used to convert any other
data type in to character...
Note: We cannot use to_char function to
change the character functions again.
Speacially in most cases of real time
we
use this to change the date datatype column format to any required
formats......
ex1: Select
to_char(trunc(sysdate),'DD-MON-YYYY') from dual;
Select to_char(trunc(sysdate),'DD-MON-YYYY
HH24:mi:ss') from dual;
Select to_char(trunc(sysdate),'DD-MON-YYYY
HH:mi:ss') from dual;
Ex2: select to_char(trunc(sysdate),'DD') from dual;
Ex3: select to_char(trunc(sysdate),'MON') from dual;
Ex4: select to_char(trunc(sysdate),'YYYY') from dual;
Ex5: select to_char(trunc(sysdate),'yyyy/mm/dd') from
dual;
Ex6: select to_char(trunc(sysdate),'mon-yy') from dual;
----------------------------
Select sysdate
,to_char(trunc(sysdate),'DD-MON-YYYY') a
,to_char(trunc(sysdate),'DD') b
,to_char(trunc(sysdate),'MON') c
,to_char(trunc(sysdate),'YYYY') d
,to_char(trunc(sysdate),'yyyy/mm/dd') e
,to_char(trunc(sysdate),'DD/MON-YYYY') f
,to_char(trunc(sysdate),'mon-yy') g
from dual;
-----------------------------
Ex7: to retreive no of the week of the
date of that year
select
to_char(trunc(sysdate),'WW') from dual;
select
'WW'||to_char(trunc(sysdate),'WW')||'--'||to_char(sysdate,'YYYY') from
dual;
--------------------
Select hiredate
,to_char(trunc(hiredate),'DD-MON-YYYY') a
,to_char(trunc(hiredate),'DD') b
,to_char(trunc(hiredate),'MON') c
,to_char(trunc(hiredate),'YYYY') d
,to_char(trunc(hiredate),'yyyy/mm/dd') e
,to_char(trunc(hiredate),'DD/MON-YYYY') f
,to_char(trunc(hiredate),'mon-yy') g
, to_char(trunc(hiredate),'WW') h
from emp;
-----------------------------------
Print the Month and week of that date
Ex8 :
select
to_char(trunc(sysdate),'MON') Month_1
,to_char(trunc(sysdate),'WW') week_no
,to_char(trunc(sysdate),'MON')||'&'||to_char(trunc(sysdate),'WW')
from dual;
o/p ===
JUN-23
Ex9 : retreive the day of the year
select
to_char(trunc(sysdate),'DAY') from dual;
Last day of the Year
-------------------------------
select
to_char(add_months(trunc(sysdate,'year'),12)-1,'DAY') from dual
TO_DATE
This function is used to convert character
date type into Date ..
SYNTAX
---------
to_date(columns name,format)
Note: We cannot use to_date function on
the date columns.
ex: select to_date(hiredate,'YYYY-MON-DD')
from emp
the above sql stmt will error out as we
cannot apply to_date on a date datatype
Speacially in most cases of real time we
use this
to
change the date datatype column format to any required formats......
I want to change the format of hiredate
colum value as dd-mon-yyyy
and it should be the date type as date
only
first change the date column to char by
using to_char to reqired format
and then apply to_date to make it date data
type
select
to_date(to_char(hiredate,'YYYY-MON-DD'),'YYYY-MON-DD') from emp
--------------------
Practice:
Last date of this month
First date of the next month
Last date of the last month
First date of this month
15 date of this month
First date of this year.
First date of the next year
last date of this year
last day of this year. -- ?
first date of this quater
first date of this week....
----
First friday of next month
First friday of ths month
last friday of this month
-----
select last_day(trunc(sysdate)) from dual;
select last_day(trunc(sysdate))+1 from
dual;
select trunc(sysdate,'MONTH')-1 from dual;
select trunc(sysdate,'MONTH') from dual;
select trunc(sysdate,'MONTH')+14 from
dual;
select trunc(sysdate,'YEAR') from dual;
select
add_months(trunc(sysdate,'YEAR'),12) from dual;
select
add_months(trunc(sysdate,'YEAR'),12)-1 from dual;
select trunc(sysdate,'DAY')from dual;
select
next_day(last_day(trunc(sysdate)),'friday')-7from dual;
select next_day(trunc(sysdate,'MONTH'),'friday')from
dual;
select
next_day(trunc(sysdate,'MONTH'),'friday')from dual;
dual;
select trunc(sysdate,'MONTH') from dual;
select trunc(sysdate,'MONTH')+14 from
dual;
select trunc(sysdate,'YEAR') from dual;
select
add_months(trunc(sysdate,'YEAR'),12) from dual;
select
add_months(trunc(sysdate,'YEAR'),12)-1 from dual;
select trunc(sysdate,'DAY')from dual;
select
next_day(last_day(trunc(sysdate)),'friday')-7from dual;
select next_day(trunc(sysdate,'MONTH'),'friday')from
dual;
select
next_day(trunc(sysdate,'MONTH'),'friday')from dual;
No comments:
Post a Comment