DATE FUNCTIONS in oracle

Ij
Posted by Ij under Oracle category on | Points: 40 | Views : 614
SQL> select sysdate from dual;
.....25-MAR-15
SQL>select current_date from dual;
...This will returns the current date in the session’s timezone
 SQL> select current_timestamp from dual;
... This will returns the current timestamp with the active time zone information......25-MAR-15 10.22.58.472000000 AM ASIA/CALCUTTA
SQL>select systimestamp from dual;
...25-MAR-15 10.24.03.811000000 AM +05:30
[This will returns the system date, including fractional seconds and time zone of the
database.]
 SQL>select dbtimezone from dual;
..This will returns the current database time zone in UTC format. (Coordinated Universal Time)
TO_CHAR
This will be used to extract various date formats.
The available date formats as follows.
SQL>select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual;
...25 march 2015 10:29:37 am wed
SQL>select to_char(sysdate,'dd month year') from dual;
...25 march twenty fifteen
SQL> select to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day') from dual;
..25 * march * wednesday
-- If you are not using to_char oracle will display output in default date format.
SQL>select add_months(to_date('25-mar-2015','dd-mon-yyyy'), 5) from dual;
...25-AUG-15
SQL>select months_between(to_date('25-mar-2015','dd-mon-yyyy'), to_date('25-jan-2015','dd-mon-yyyy')) from dual;
.....2
SQL>select next_day(to_date('25-mar-2015','dd-mon-yyyy'),'wed') from dual;
...01-APR-15

Comments or Responses

Login to post response