Sysdate
Current_date
Current_timestamp
Systimestamp
Localtimestamp
Dbtimezone
Sessiontimezone
To_char
To_date
Add_months
Months_between
Next_day
Last_day
Extract
Greatest
Least
Round
Trunc
New_time
Coalesce
Oracle default date
format is DD-MON-YY.
We can change the default
format to our desired format by using the following command.
SQL> alter session set
nls_date_format = ‘DD-MONTH-YYYY’;
But this will expire once the session
was closed.
a)
SYSDATE
This will give the current date and time.
Ex:
SQL> select sysdate from dual;
SYSDATE
-----------
24-DEC-06
b)
CURRENT_DATE
This will returns the current date in the
session’s timezone.
Ex:
SQL> select current_date from
dual;
CURRENT_DATE
------------------
24-DEC-06
c)
CURRENT_TIMESTAMP
This will returns the current timestamp
with the active time zone information.
Ex:
SQL> select current_timestamp
from dual;
CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-DEC-06
03.42.41.383369 AM +05:30
d)
SYSTIMESTAMP
This will returns the system date,
including fractional seconds and time zone of the database.
Ex:
SQL> select systimestamp from
dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06
03.49.31.830099 AM +05:30
e)
LOCALTIMESTAMP
This will returns local timestamp in the
active time zone information, with no time zone information shown.
Ex:
SQL> select
localtimestamp from dual;
LOCALTIMESTAMP
------------------------------------------
24-DEC-06
03.44.18.502874 AM
f)
DBTIMEZONE
This will returns the current database time
zone in UTC format. (Coordinated Universal Time)
Ex:
SQL> select dbtimezone
from dual;
DBTIMEZONE
---------------
-07:00
g)
SESSIONTIMEZONE
This will returns the value of the current
session’s time zone.
Ex:
SQL> select sessiontimezone from
dual;
SESSIONTIMEZONE
---------------------------------
+05:30
h)
TO_CHAR
This
will be used to extract various date formats.
The available date formats as follows.
Syntax:
to_char (date, format)
DATE
FORMATS
D -- No of days in week
DD --
No of days in month
DDD -- No of days in year
MM -- No of month
MON -- Three letter abbreviation of month
MONTH --
Fully spelled out month
RM -- Roman numeral month
DY -- Three letter abbreviated day
DAY -- Fully spelled out day
Y -- Last one digit of the year
YY -- Last two digits of the year
YYY -- Last three digits of the year
YYYY -- Full four digit year
SYYYY --
Signed year
I -- One digit year from ISO standard
IY -- Two digit year from ISO standard
IYY -- Three digit year from ISO standard
IYYY -- Four digit year from ISO standard
Y, YYY -- Year with comma
YEAR -- Fully spelled out year
CC -- Century
Q -- No of quarters
W -- No of weeks in month
WW -- No of weeks in year
IW -- No of weeks in year from ISO standard
HH -- Hours
MI -- Minutes
SS -- Seconds
FF --
Fractional seconds
AM or PM -- Displays AM or PM depending upon time
of day
A.M or P.M -- Displays A.M or P.M depending upon
time of day
AD or BC -- Displays AD or BC depending upon the
date
A.D or B.C -- Displays AD or BC depending upon the
date
FM -- Prefix to month or day, suppresses
padding of month or day
TH -- Suffix to a number
SP -- suffix to a number to be spelled out
SPTH -- Suffix combination of TH and SP to be
both spelled out
THSP -- same as SPTH
Ex:
SQL> select to_char(sysdate,'dd month
yyyy hh:mi:ss am dy') from dual;
TO_CHAR(SYSDATE,'DD
MONTH YYYYHH:MI
----------------------------------------------------
24
december 2006 02:03:23 pm sun
SQL> select to_char(sysdate,'dd month
year') from dual;
TO_CHAR(SYSDATE,'DDMONTHYEAR')
-------------------------------------------------------
24
december two thousand six
SQL> select to_char(sysdate,'dd fmmonth
year') from dual;
TO_CHAR(SYSDATE,'DD
FMMONTH YEAR')
-------------------------------------------------------
24
december two thousand six
SQL> select to_char(sysdate,'ddth
DDTH') from dual;
TO_CHAR(S
------------
24th
24TH
SQL> select to_char(sysdate,'ddspth
DDSPTH') from dual;
TO_CHAR(SYSDATE,'DDSPTHDDSPTH
------------------------------------------
twenty-fourth
TWENTY-FOURTH
SQL> select to_char(sysdate,'ddsp Ddsp
DDSP ') from dual;
TO_CHAR(SYSDATE,'DDSPDDSPDDSP')
------------------------------------------------
twenty-four
Twenty-Four TWENTY-FOUR
i)
TO_DATE
This will be used to convert the string
into data format.
Syntax:
to_date (date)
Ex:
SQL> select
to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day') from dual;
TO_CHAR(TO_DATE('24/DEC/20
--------------------------
24
* december * Sunday
-- If you are not using
to_char oracle will display output in default date format.
j)
ADD_MONTHS
This will add the specified months to the
given date.
Syntax:
add_months (date, no_of_months)
Ex:
SQL> select
add_months(to_date('11-jan-1990','dd-mon-yyyy'), 5) from dual;
ADD_MONTHS
----------------
11-JUN-90
SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'),
-5) from dual;
ADD_MONTH
---------------
11-AUG-89
If no_of_months is zero then it will display the same date.
If no_of_months is null then it will display
nothing.
k)
MONTHS_BETWEEN
This will give difference of months between
two dates.
Syntax:
months_between (date1, date2)
Ex:
SQL> select
months_between(to_date('11-aug-1990','dd-mon-yyyy'), to_date('11-jan-
1990','dd-mon-yyyy')) from dual;
MONTHS_BETWEEN(TO_DATE('11-AUG-1990','DD-MON-YYYY'),TO_DATE('11-JAN-1990','DD-MON-YYYY'))
-----------------------------------------------------------------------------
7
SQL> select
months_between(to_date('11-jan-1990','dd-mon-yyyy'), to_date('11-aug-1990','dd-mon-yyyy'))
from dual;
MONTHS_BETWEEN(TO_DATE('11-JAN-1990','DD-MON-YYYY'),TO_DATE('11-AUG-1990','DD-MON-YYYY'))
----------------------------------------------------------------------------
-7
l)
NEXT_DAY
This will produce next day of the given day
from the specified date.
Syntax: next_day (date, day)
Ex:
SQL> select
next_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;
NEXT_DAY(
-------------
31-DEC-06
-- If the day parameter
is null then it will display nothing.
m)
LAST_DAY
This will produce last day of the given
date.
Syntax: last_day (date)
Ex:
SQL> select
last_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;
LAST_DAY(
-------------
31-DEC-06
n)
EXTRACT
This is used to extract a portion of the
date value.
Syntax: extract ((year | month | day | hour
| minute | second), date)
Ex:
SQL> select extract(year from
sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------------------
2006
-- You can extract only
one value at a time.
o)
GREATEST
This will give the greatest date.
Syntax:
greatest (date1, date2, date3 … daten)
Ex:
SQL> select
greatest(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-mon-
yy'),to_date('11-apr-90','dd-mon-yy')) from dual;
GREATEST(
-------------
11-APR-90
p)
LEAST
This will give the least date.
Syntax: least (date1, date2, date3 …
daten)
Ex:
SQL> select
least(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-mon-yy'),to_date('11-apr-90','dd-mon-yy'))
from dual;
LEAST(
-------------
11-JAN-90
q)
ROUND
Round will rounds the date to which it was
equal to or greater than the given date.
Syntax:
round (date, (day | month | year))
If the second parameter was year then round
will checks the month of the given date in the following ranges.
JAN -- JUN
JUL -- DEC
If the month falls between JAN and JUN then
it returns the first day of the current year.
If the month falls between JUL and DEC then
it returns the first day of the next year.
If the second parameter was month then
round will checks the day of the given date in the
following ranges.
1 -- 15
16 -- 31
If the day falls between 1 and 15 then it
returns the first day of the current month.
If the day falls between 16 and 31 then it
returns the first day of the next month.
If the second parameter was day then round
will checks the week day of the given date in
the following ranges.
SUN -- WED
THU -- SUN
If the week day falls between SUN and WED
then it returns the previous Sunday.
If the weekday falls between THU and SUN
then it returns the next Sunday.
If the second parameter was null then it returns nothing.
If the you are not
specifying the second parameter then round will resets the time to the beginning
of the current day in case of user specified date.
If the you are not
specifying the second parameter then round will resets the time to the beginning
of the next day in case of sysdate.
Ex:
SQL> select
round(to_date('24-dec-04','dd-mon-yy'),'year'), round(to_date('11-mar-06','dd-mon-yy'),'year')
from dual;
ROUND(TO_
ROUND(TO_
------------ ---------------
01-JAN-05 01-JAN-06
SQL> select
round(to_date('11-jan-04','dd-mon-yy'),'month'),
round(to_date('18-jan-04','dd-mon-yy'),'month')
from dual;
ROUND(TO_
ROUND(TO_
------------- ---------------
01-JAN-04 01-FEB-04
SQL> select
round(to_date('26-dec-06','dd-mon-yy'),'day'), round(to_date('29-dec-06','dd-mon-yy'),'day')
from dual;
ROUND(TO_
ROUND(TO_
-------------- --------------
24-DEC-06 31-DEC-06
SQL> select
to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss am') from
dual;
TO_CHAR(ROUND(TO_DATE('
---------------------------------
24
dec 2006 12:00:00 am
r)
TRUNC
Trunc will chops off the date to which it
was equal to or less than the given date.
Syntax: trunc (date, (day | month | year))
If the second parameter
was year then it always returns the first day of the current year.
If the second parameter
was month then it always returns the first day of the current month.
If the second parameter
was day then it always returns the previous sunday.
If the second parameter
was null then it returns nothing.
If the you are not specifying
the second parameter then trunk will resets the time to the begining of the
current day.
Ex:
SQL> select
trunc(to_date('24-dec-04','dd-mon-yy'),'year'), trunc(to_date('11-mar-06','dd-mon-yy'),'year')
from dual;
TRUNC(TO_
TRUNC(TO_
------------- --------------
01-JAN-04 01-JAN-06
SQL> select
trunc(to_date('11-jan-04','dd-mon-yy'),'month'), trunc(to_date('18-jan-04','dd-mon-yy'),'month')
from dual;
TRUNC(TO_
TRUNC(TO_
------------- -------------
01-JAN-04 01-JAN-04
SQL> select
trunc(to_date('26-dec-06','dd-mon-yy'),'day'), trunc(to_date('29-dec-06','dd-mon-yy'),'day')
from dual;
TRUNC(TO_
TRUNC(TO_
------------- --------------
24-DEC-06
24-DEC-06
SQL> select
to_char(trunc(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss am') from
dual;
TO_CHAR(TRUNC(TO_DATE('
---------------------------------
24
dec 2006 12:00:00 am
s)
NEW_TIME
This will give the desired timezone’s date
and time.
Syntax: new_time (date, current_timezone,
desired_timezone)
Available timezones are as follows.
TIMEZONES
AST/ADT -- Atlantic standard/day light time
BST/BDT -- Bering standard/day light time
CST/CDT -- Central standard/day light time
EST/EDT -- Eastern standard/day light time
GMT -- Greenwich mean time
HST/HDT -- Alaska-Hawaii standard/day light time
MST/MDT -- Mountain standard/day light time
NST -- Newfoundland standard time
PST/PDT -- Pacific standard/day light time
YST/YDT -- Yukon standard/day light time
Ex:
SQL> select
to_char(new_time(sysdate,'gmt','yst'),'dd mon yyyy hh:mi:ss am') from dual;
TO_CHAR(NEW_TIME(SYSDAT
-----------------------------------
24
dec 2006 02:51:20 pm
SQL> select to_char(new_time(sysdate,'gmt','est'),'dd mon yyyy
hh:mi:ss am') from dual;
TO_CHAR(NEW_TIME(SYSDAT
-----------------------
24
dec 2006 06:51:26 pm
t)
COALESCE
This will give the first non-null date.
Syntax:
coalesce (date1, date2, date3 … daten)
Ex:
SQL> select
coalesce('12-jan-90','13-jan-99'), coalesce(null,'12-jan-90','23-mar-98',null) from
dual;
COALESCE(
COALESCE(
------------- ------------
12-jan-90 12-jan-90
MISCELLANEOUS
FUNCTIONS
Uid
User
Vsize
Rank
Dense_rank
a)
UID
This will returns the integer value
corresponding to the user currently logged in.
Ex:
SQL> select uid from dual;
UID
-------
319
b)
USER
This will returns the login’s user name.
Ex:
SQL> select user from dual;
USER
----------------
SAKETH
c)
VSIZE
This will returns the number of bytes in
the expression.
Ex:
SQL> select vsize(123), vsize('computer'),
vsize('12-jan-90') from dual;
VSIZE(123)
VSIZE('COMPUTER') VSIZE('12-JAN-90')
------------- ----------------------- ----------------------
3 8 9
d)
RANK
This will give the non-sequential ranking.
Ex:
SQL> select rownum,sal from
(select sal from emp order by sal desc);
ROWNUM
SAL
---------- ----------
1
5000
2
3000
3
3000
4
2975
5
2850
6
2450
7
1600
8
1500
9
1300
10
1250
11
1250
12
1100
13
1000
14 950
15 800
SQL> select rank(2975) within
group(order by sal desc) from emp;
RANK(2975)WITHINGROUP(ORDERBYSALDESC)
---------------------------------------------------------
4
d)
DENSE_RANK
This will give the sequential ranking.
Ex:
SQL> select dense_rank(2975) within
group(order by sal desc) from emp;
DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)
-----------------------------------------------------------------
3
CONVERSION
FUNCTIONS
Bin_to_num
Chartorowid
Rowidtochar
To_number
To_char
To_date
a)
BIN_TO_NUM
This will convert the binary value to its
numerical equivalent.
Syntax: bin_to_num( binary_bits)
Ex:
SQL> select bin_to_num(1,1,0) from
dual;
BIN_TO_NUM(1,1,0)
------------------------
6
If all the bits are zero then it produces
zero.
If all the bits are null then it produces an
error.
b)
CHARTOROWID
This will convert a character string to
act like an internal oracle row identifier or rowid.
c)
ROWIDTOCHAR
This will convert an internal oracle row
identifier or rowid to character string.
d)
TO_NUMBER
This will convert a char or varchar to
number.
e)
TO_CHAR
This will convert a number or date to
character string.
f)
TO_DATE
This will convert a number, char or varchar
to a date.
No comments:
Post a Comment