Oracle Database
Contents
In Oracle, converting DateTime to String is using TO_CHAR function. As for parsing String to DateTime, we use TO_DATE.
SELECT TO_CHAR(current_timestamp, 'DD-MON-YYYY') from dual; -- Output: 21-March-2015 SELECT TO_DATE('19-FEB-2015', 'DD-MON-YYYY') from dual; -- Output: 19-FEB-15
The complete list of symbols that can be used are listed below (prettified, original source can be found HERE):
Element | Specify in TO_* datetime functions? | Description |
---|---|---|
- / , . ; : "text" | Yes | Punctuation and quoted text is reproduced in the result. |
AD A.D. | Yes | AD indicator with or without periods. |
AM A.M. | Yes | Meridian indicator with or without periods. |
BC B.C. | Yes | BC indicator with or without periods. |
CC SCC | No | Century.
For example, 2002 returns 21; 2000 returns 20. |
D | Yes | Day of week (1-7). |
DAY | Yes | Name of day, padded with blanks to display width of the widest name of day in the date language used for this element. |
DD | Yes | Day of month (1-31). |
DDD | Yes | Day of year (1-366). |
DL | Yes | Returns a value in the long date format, which is an extension of Oracle Database’s DATE format (the current value of the NLS_DATE_FORMAT parameter). Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘fmDay, Month dd, yyyy’. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy’. Restriction: You can specify this format only with the TS element, separated by white space. |
DS | Yes | Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR’. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR’. Restriction: You can specify this format only with the TS element, separated by white space. |
DY | Yes | Abbreviated name of day. |
E | No | Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE | No | Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
FF [1..9] | Yes | Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime datatype or the datatype’s default precision. Examples: ‘HH:MI:SS.FF’ SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual; |
FM | Yes | Returns a value with no leading or trailing blanks. |
FX | Yes | Requires exact matching between the character data and the format model. |
HH | Yes | Hour of day (1-12). |
HH12 | No | Hour of day (1-12). |
HH24 | Yes | Hour of day (0-23). |
IW | No | Week of year (1-52 or 1-53) based on the ISO standard. |
IYY IY I | No | Last 3, 2, or 1 digit(s) of ISO year. |
IYYY | No | 4-digit year based on the ISO standard. |
J | Yes | Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers. |
MI | Yes | Minute (0-59). |
MM | Yes | Month (01-12; January = 01). |
MON | Yes | Abbreviated name of month. |
MONTH | Yes | Name of month, padded with blanks to display width of the widest name of month in the date language used for this element. |
PM P.M. | No | Meridian indicator with or without periods. |
Q | No | Quarter of year (1, 2, 3, 4; January – March = 1). |
RM | Yes | Roman numeral month (I-XII; January = I). |
RR | Yes | Lets you store 20th century dates in the 21st century using only two digits. |
RRRR | Yes | Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year. |
SS | Yes | Second (0-59). |
SSSSS | Yes | Seconds past midnight (0-86399). |
TS | Yes | Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters. Restriction: You can specify this format only with the DL or DS element, separated by white space. |
TZD | Yes | Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information. It must correspond with the region specified in TZR. Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time). |
TZH | Yes | Time zone hour. (See TZM format element.) Example: ‘HH:MI:SS.FFTZH:TZM’. |
TZM | Yes | Time zone minute. (See TZH format element.) Example: ‘HH:MI:SS.FFTZH:TZM’. |
TZR | Yes | Time zone region information. The value must be one of the time zone regions supported in the database. Example: US/Pacific |
WW | No | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
W | No | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
X | Yes | Local radix character. Example: ‘HH:MI:SSXFF’. |
Y,YYY | Yes | Year with comma in this position. |
YEAR SYEAR | No | Year, spelled out; S prefixes BC dates with a minus sign (-). |
YYYY SYYYY | Yes | 4-digit year; S prefixes BC dates with a minus sign. |
YYY YY Y | Yes | Last 3, 2, or 1 digit(s) of year. |
a WordPress rating system
About Hardono
Incoming Search
.net, c#, mssql, mysql, oracle