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>
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
-
-
4096 Columns3 weeks ago
-
-
-
-
-
-
Oracle Cloud & Third party tools4 years ago
-
-
-
Moving Sideways8 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment