Wednesday, June 9, 2010

Date Format in oracle

Date Formats of Oracle Language
==============================
==============================


Format mask Description
========== ==============
CC : Century
SCC : Century BC prefixed with -
YYYY :Year with 4 numbers
SYYY :Year BC prefixed with -
IYYY :ISO Year with 4 numbers
YY :Year with 2 numbers
RR :Year with 2 numbers with Y2k compatibility
YEAR :Year in characters
SYEAR :Year in characters, BC prefixed with -
BC :BC/AD Indicator *
Q :Quarter in numbers (1,2,3,4)
MM :Month of year 01, 02...12
MONTH :Month in characters (i.e. January)
MON :JAN, FEB
WW :Weeknumber (i.e. 1)
W :Weeknumber of the month (i.e. 5)
IW :Weeknumber of the year in ISO standard.
DDD :Day of year in numbers (i.e. 365)
DD :Day of the month in numbers (i.e. 28)
D :Day of week in numbers(i.e. 7)
DAY :Day of the week in characters (i.e. Monday)
FMDAY :Day of the week in characters (i.e. Monday)
DY :Day of the week in short character description (i.e. SUN)
J :Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle)
HH :Hournumber of the day (1-12)
HH12 :Hournumber of the day (1-12)
HH24 :Hournumber of the day with 24Hours notation (0-23)
AM :AM or PM
PM :AM or PM
MI :Number of minutes (i.e. 59)
SS :Number of seconds (i.e. 59)
SSSSS :Number of seconds this day.
DS :Short date format. Depends on NLS-settings. Use only with timestamp.
DL :Long date format. Depends on NLS-settings. Use only with timestamp.
E :Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only)
EE :The full era name
FF :The fractional seconds. Use with timestamp.
FF1..FF9 ;The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds.
FM :Fill Mode: suppresses blianks in output from conversion
FX :Format Exact: requires exact pattern matching between data and format model.
IYY or IY or I :the last 3,2,1 digits of the ISO standard year. Output only
RM :The Roman numeral representation of the month (I .. XII)
RR :The last 2 digits of the year.
RRRR :The last 2 digits of the year when used for output. Accepts fout-digit years when used for input.
SCC :Century. BC dates are prefixed with a minus.
CC :Century
SP :Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten"
SPTH :Spelled and ordinal format; 1 results in first.
TH :Converts a number to it's ordinal format. For example 1 becoms 1st.
TS :Short time format. Depends on NLS-settings. Use only with timestamp.
TZD :Abbreviated time zone name. ie PST.
TZH :Time zone hour displacement.
TZM :Time zone minute displacement.
TZR :Time zone region
X :Local radix character. In america this is a period (.)

================= ==== ====================================

Some Examples:
-----------------------

SQL>
SQL>
SQL> select to_char(sysdate,'CC') from dual;

TO
--
21

SQL>
SQL> select to_char(sysdate,'YYYY') from dual;

TO_C
----
2010

SQL>
SQL> select to_char(sysdate,'YEAR') from dual;

TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWENTY TEN

SQL>
SQL> select to_char(sysdate,'MONTH') from dual;

TO_CHAR(S
---------
JUNE

SQL>
SQL> select to_char(sysdate,'BC') from dual;

TO
--
AD

SQL>
SQL> select to_char(sysdate,'RM') from dual;

TO_C
----
VI

SQL>
SQL> select to_char(sysdate,'Q') from dual;

T
-
2

SQL>

No comments: