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.
1 If the second parameter was null then
it returns nothing.
2 If the you are not specifying the
second parameter then round will resets the time to the begining of the current
day in case of user specified date.
3 If the you are not specifying the
second parameter then round will resets the time to the begining 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))
1 If the second parameter was year
then it always returns the first day of the current year.
2 If the second parameter was month
then it always returns the first day of the current month.
3 If the second parameter was day
then it always returns the previous sunday.
4 If the second parameter was null then
it returns nothing.
5 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
59 Uid
60 User
61 Vsize
62 Rank
63 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
64 Bin_to_num
65 Chartorowid
66 Rowidtochar
67 To_number
68 To_char
69 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
1 If all the bits are zero then it
produces zero.
2 If all the bits are null then it
produces an error.
b) CHARTOROWID
No comments:
Post a Comment