oracle sql and dba tutorial logo

Oracle DBA

Learn Oracle 11g / 12c Database Admin step by step



Oracle SQL

Tutorial for Oracle SQL

 

Oracle DBA Interview Questions

Most asked Oracle DBA Interview Questions.

60 Technical Questions
42 Backup & Recovery Questions

Unix For Oracle DBA 20 Questions


Download Oracle 11g / 12 c Software

Links to Download Oracle 12c / 11g for Linux, Windows etc.



Formating DATES in Oracle

Date Functions and Operators.

To see the system date and time use the following functions :

CURRENT_DATE    :returns the current date in the session time zone, in a value in the Gregorian calendar of datatype
                                   DATE
SYSDATE                 :Returns the current date and time.
SYSTIMESTAMP    :The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone
                                    of the database. The return type is TIMESTAMP WITH TIME ZONE.

SYSDATE Example

To see the current system date and time give the following query.

select sysdate from dual;

SYSDATE
-------
8-AUG-03

The format in which the date is displayed depends on NLS_DATE_FORMAT parameter.

For example set the NLS_DATE_FORMAT to the following format

alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH:MIpm’;

Then give the give the following statement

select sysdate from dual;

SYSDATE
------------------
8-AUG-2003 03:05pm

The default setting of NLS_DATE_FORMAT is DD-MON-YY


CURRENT_DATE Example

To see the current system date and time with  time zone use CURRENT_DATE function

ALTER SESSION SET TIME_ZONE = '-4:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-04:00          22-APR-2003 14:15:03

ALTER SESSION SET TIME_ZONE = '-7:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-07:00          22-APR-2003 09:15:33

 

SYSTIMESTAMP Example

To see the current system date and time with fractional seconds with time zone give the following statement

select systimestamp from dual;

SYSTIMESTAMP
-------------------------------
22-APR-03 08.38.55.538741 AM -07:00

 

DATE FORMAT MODELS

To translate the date into a different format string  you can use TO_CHAR function with date format. For example to see the current day you can give the following query

Select to_char(sysdate,’DAY’)”Today” FROM DUAL;

TODAY
-------
THURSDAY

To translate a character value, which is in format other than the default date format, into a date value you can use TO_DATE function with date format to date

Like this “DAY” format model there are many other date format models available in Oracle. The following table list date format models.

FORMAT

MEANING

D

Day of the week

DD

Day of the month

DDD

Day of the year

DAY

Full day for ex. ‘Monday’, ’Tuesday’, ’Wednesday’

DY

Day in three letters for ex. ‘MON’, ‘TUE’,’FRI’

W

Week of the month

WW

Week of the year

MM

Month in two digits  (1-Jan, 2-Feb,…12-Dec)

MON

Month in three characters like “Jan”, ”Feb”, ”Apr”

MONTH

Full Month like “January”, ”February”, ”April”

RM

Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)

Q

Quarter of the Month

YY

Last two digits of the year.

YYYY

Full year

YEAR

Year in words like “Nineteen Ninety Nine”

HH

Hours in 12 hour format

HH12

Hours in 12 hour format

HH24

Hours in 24 hour format

MI

Minutes

SS

Seconds

FF

Fractional Seconds

SSSSS

Milliseconds

J

Julian Day i.e Days since 1st-Jan-4712BC to till-date

RR

If the year is less than 50 Assumes the year as 21ST Century. If the year is greater than 50 then assumes the year in 20th Century.

 

suffixes

TH

Returns th, st, rd or nd according to the leading number like 1st , 2nd 3rd 4th

SP

Spells out the leading number

AM or PM

Returns AM or PM according to the time

SPTH

Returns Spelled Ordinal number. For. Example First, Fourth

For example to see the today’s date in the following format

Friday, 7th March, 2014 

Give the following statement

select to_char(sysdate,’Day, ddth Month, yyyy’)”Today” from dual;

TODAY
------------------------
Friday, 7th March, 2014 

For example you want to see hire dates of all employee in the following format

Friday, 8th August, 2003 

Then give the following query.

select to_char(hire_date,’Day, ddth Month, yyyy’) from emp;

 

TO_DATE Example

To_Date function is used to convert strings into date values. For example you want to see what was the day on 15-aug-1947. The use the to_date function to first convert the string into date value and then pass on this value to to_char function to extract day.

select to_char(to_date(’15-aug-1947’,’dd-mon-yyyy’),’Day’)
                                         from dual;

TO_CHAR(
--------
Friday

To see how many days have passed since 15-aug-1947 then give the following query

select sysdate-to_date(’15-aug-1947’,’dd-mon-yyyy’) from dual;

Now we want to see which date will occur after 45 days from now

select sysdate+45 from dual;

SYSDATE
-------
06-JUN-2003


ADD_MONTHS

To see which date will occur after 6 months from now, we can use ADD_MONTHS function

Select ADD_MONTHS(SYSDATE,6) from dual;

ADD_MONTHS
----------
22-OCT-2003

 

MONTHS_BETWEEN

To see how many months have passed since  a particular date, use the MONTHS_BETWEEN function.

For Example, to see how many months have passed since 15-aug-1947, give the following query.

select months_between(sysdate,to_date(’15-aug-1947’))
                             from dual;

Months
------
616.553

To eliminate the decimal value use truncate function

select trunc(months_between(sysdate,to_date(’15-aug-1947’)))
                             from dual;

Months
------
616

 

LAST_DAY

To see the last date of the month of a given date, Use LAST_DAY function.

select LAST_DAY(sysdate) from dual;

LAST_DAY
--------
31-AUG-2003

 

NEXT_DAY

To see when a particular day is coming next ,  use the NEXT_DAY function.

For Example to view when next Saturday is coming, give the following query

select next_day(sysdate) from dual;

NEXT_DAY
-----------
09-AUG-2003

 

EXTRACT

An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation

The syntax of EXTRACT function is

EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

Example
The following demonstrate the usage of EXTRACT function to extract year from current date.

select extract(year from sysdate) from dual;

EXTRACT
-------
2003

 

 


HomeContact Us

Data Loader

Data Loader is a simple yet powerful tool to
export and import Data between many common database formats


Forms Data Loader

Tool to load data into Oracle E-Business Suite R12 / Oracle Apps using Macros and Forms Record and Playback

Interface Computers Academy © 2007-2017 All Rights Reserved