Oracle Articles, Oracle Tools, Oracle Tips, Database Articles and DBA Tips  

The Largest Online Resource for Oracle Articles, Oracle Tips, Oracle Scripts & Oracle Tools!!


Enter your Email:
 
Navigate at FreeMegaZone Home      Articles      Tools      Jobs      Games      Support      Submit Content      Advertise
Advertise at http://www.articles.freemegazone.com

Advertise at FreeMegaZone

Give tremendous boost to your business by advertising at FreeMegaZone. Contact webmaster@freemegazone.com
 


Rating: *****                                             Rate this article:    

It is very important to keep in mind the version of Oracle server you are using at the time of representation of Oracle date – time formats. Oracle date formats in earlier versions of Oracle were represented as the datatype date which was precise up to one sec. Similarly time intervals were taken as numbers where number was equal to one day. From Oracle version 9 onwards it has became possible to represent Oracle date – time points in the form of time and time intervals while using ANSI SQL data types likes interval and timestamp .

Oracle Date Formats used by TO_CHAR or TO_DATE functions:

Oracle functions TO_CHAR and TO_DATE returns a formatted Oracle date or Oracle time string.

TRUNC: Using TRUNC in Oracle date formats by TO_CHAR or TO_DATE functions returns 1 st day of the period.

ROUND: Using ROUND in Oracle date formats by TO_CHAR or TO_DATE functions rounds up the values at mid-year or mid-month (July 1 or 16th day).

Using TO_CHAR with SYSDATE:

By default DATE value is returned by the function “SYSDATE” as current date & current time of the system.

Oracle Date Formats:

ORACLE DATE FORMATS FOR CENTURIES

CC: Outputs Century
SCC: Outputs Century BC prefixed with -

ORACLE DATE FORMATS FOR YEARS

YYYY: Outputs Year 1956
SYYY: Outputs Year BC prefixed with -
IYYY: Outputs ISO Year 1956
YY : Outputs Year 56
RR: Outputs Year 56 rollover for Y2K compatibility
RRRR: Outputs Year rollover by (accepting 2 digits & returning 4 digits
YEAR: Outputs Year spelled out
SYEAR: Outputs Year spelled out BC prefixed with -
BC : Outputs AD/BC Indicator

ORACLE DATE FORMATS FOR MONTHS

Q: Outputs Quarter For example Jan-Mar=1, Apr-Jun=2, Jul-Sep=3, Oct-Dec=4
MM: Outputs Month of year For example 01, 02, 03, 04, ...12
MON: Outputs Month of the year in short. For example JAN, FEB, MAR…
MONTH: Outputs Month of the year in full. For example January, February, …, December
FMMONTH: Outputs Month of the year in full. For example January, February, …, December
RM: Outputs Roman Month of the year. For example I, II, III, IV, ...XII

ORACLE DATE FORMATS FOR WEEKS

WW: Outputs Week of the year. For example 1-52
W: Outputs Week of the month. For example 1-5
IW: Outputs ISO std week of the year

ORACLE DATE FORMATS FOR DAYS

DDD: Outputs Day of year. For example 1-366
DD: Outputs Day of month. For example 1-31
D: Outputs Day of week. For example 1-7
DAY: Outputs Day of week in full. For example Monday, Tuesday,...,Sunday
FMDAY: Outputs Day of week in full. For example Monday, Tuesday,...,Sunday
DY: Outputs Day of week In short. For example MON...SUN
DDTH: Outputs ordinal day For example 7TH
DDSPTH: Outputs spelled out ordinal For example SEVENTH
J: Outputs Julian Day (days since 31/12/4713)

ORACLE DATE FORMATS FOR HOURS

HH: Outputs Hours of day For example 1-12
HH12: Outputs Hours of day For example 1-12
HH24: Outputs Hours of day For example 1-24
SPHH: Outputs SEVEN
AM: Outputs pm or am of hour
PM: Outputs pm or am of hour
A.M.: Outputs p.m. or a.m. of hour
P.M.: Outputs p.m. or a.m. or hour
TH : Convert and outputs to the ordinal format. For example 1 is converted to 1st

ORACLE DATE FORMATS FOR MINUTES

MI: Outputs Minutes For example 0-59
SS: Outputs Seconds For example 0-59
SSSSS: Outputs Seconds past midnight For example 0-86399

ORACLE DATE FORMATS FOR TIME

TZD: Outputs abbreviated time zone name. For example PST
TZH: Outputs Time zone hour displacement
TZM: Outputs Time zone minute displacement
TZR: Outputs Time zone region

Oracle Date Formats marked with using Punctuations and Characters:

The punctuations - / , . ; : can be used in any Oracle date format. The characters other than these can be used while including in quotes.

Oracle Date Formats by spelling:

If Oracle date formats are spelled in the form of characters then capitalization of the Oracle date format will be adopted. For example:

'MONTH' = MARCH
'Month' = March

Oracle Date Formats starting with:

Oracle date formats that are started with FM, 0's or blanks are suppressed. For example:

TO_CHAR(SYSDATE, 'FMMONTH DD, YYYY'); will return 'March 1, 2010' not 'March 01 2010'

Oracle Date Formats marked with ‘*':

Only TO_CHAR or TO_DATE date formats can be used with formats of dates that are marked with ‘*'. The functions TRUNC() and ROUND() cannot be used with such date formats of Oracle. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Recovery from an Unplanned Outage!!
   Bad DBA - Bad Practices
   DBA Tips: Limiting User Commands, Roles & Sessions!!
   Testing Database Security


 
 
HOME      ABOUT US      SUPPORT      SITE MAP      PRIVACY POLICY      TERMS OF USE      SUBMIT CONTENT      ADVERTISE
Copyright © 2007 - 2010 Oriole Intellect Inc. All rights reserved.

The name Oracle is a trademark of Oracle Corporation. Any other names used on this website may be trademarks of their respective owners