Oracle Date Formats used by TO_CHAR or TO_DATE functions :
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 :
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 :
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. *********************************************************************************************************************
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!
出ã—ã¦è‰¯ã„ã®è´…æ²¢å“ã€ä¸‡ç‰©ã“ã“ã‹ã‚‰å¸°ã£ã¦å‰µæ¥ã®è€ƒãˆãŒèŠ½ç”Ÿãˆã¦ã„ãŸã€‚