«
»

Oracle : Date and Time Formats with example

Share
In this blog, we will learn Date and Time Formants with example in Oracle.

The date and time formants like the TO_CHAR and TO_DATE function in Oracle. The date and time formants is very important and useful in Oracle. 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. The TO_DATE function converts a string to a date.
TRUNC : Using TRUNC in Oracle date formats by TO_CHAR or TO_DATE functions returns 1st 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).

Syntax :
TO_CHAR(Expression, [‘Format’], [Nls_lang])
TO_DATE(Expression, [ Format], [ nls_lang ] )

Keyword :

TO_CHAR                       : The Date, Number or Expression to convert
Format                               : Format To USE
Nls_lang                           : The international language to use.
TO_CHAR Function   : The TO_CHAR function converts a number or date to a string. The interval value of DATE,TIMESTAMP,TIMESTAMP WITH TIME ZONE, OR TIMESTAMP WITH LOCAL TIME ZONE etc.

Example : In this example, we will use TO_CHAR with SYSDATE.  By default DATE value is returned by the function “SYSDATE” as current date & current time of the system.

SQL> Select To_char(sysdate, ‘yyyy/mm/dd’) FROM dual;
SQL> Select To_char(sysdate, ‘FMMonth DD, YYYY’) FROM dual;
SQL> select To_char(sysdate,’HH24:MI:SS’) “Time Now” from dual;

Output :

Oracle Date time session

TO_DATE Function : TO_DATE converts char of CHAR, VARCHAR2,NCHAR OR NVARCHAR2 datatype to a value of DATE datatype. The fmt is a datetime model format specifying the format of char.

Example :

SQL> SELECT
2 To_char(add_months(to_date('28-SEP-2010', 'DD-MON-YYYY'),1 * 12),'DD-MON-YYYY') y2000,
3 To_char(add_months(to_date('28-SEP-2010', 'DD-MON-YYYY'),2 * 12),'DD-MON-YYYY') y2003,
4 To_char(add_months(to_date('28-SEP-2010', 'DD-MON-YYYY'),7 * 12),'DD-MON-YYYY') y2007,
5 To_char(add_months(to_date('28-SEP-2010', 'DD-MON-YYYY'),52 * 12),'DD-MON-YYYY') y2025
6 FROM
7 DUAL;

Output :

Time and date session oracle example

Date and Time Formats in Oracle :


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


 

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.
*********************************************************************************************************************

 

Share

Comments

comments

This entry was posted on Saturday, January 12th, 2013 at 2:01 am and is filed under Oracle Queries. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Oracle : Date and Time Formats with example”

  1. Moncler Jackets says:
    January 13, 2013 at 2:26 pm

    Please tell me that youre going to keep this up! Its so great and so important. I cant wait to read far more from you. I just really feel like you know so very much and know how to make people listen to what you have to say. This blog is just also cool to become missed. Great things, really. Please, PLEASE keep it up!

    Reply
  2. バーバーリコピー says:
    January 16, 2013 at 11:00 am

    出して良いの贅沢品、万物ここから帰って創業の考えが芽生えていた。

    Reply

Leave a Reply