Saturday, March 12, 2016

NUMBER AND CHARACTER FUNCTIONS

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

How to improve blog performance

Improving the performance of a blog can involve a variety of strategies, including optimizing the website's technical infrastructure, im...