Use of date formats in the ORACLE To_char () function

The following examples illustrate:

Sql> Set Linesize 200

Sql> Show Parameter Nls_date_language

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Nls_date_language string AMERICAN

Sql> Show Parameter Nls_date_format

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Nls_date_format string DD-MON-RR

Sql> select Sysdate from dual; --When no output format is used, it is the default date format DD-MON-RR, the date description language is also the default date in the language American

Sysdate

------------

04-dec-15

Sql> Select To_char (sysdate, ' Yyyy-mm-dd ') from dual; --Specify the output format as ' Yyyy-mm-dd '

To_char (sysdate, ' YYY

--------------------

2015-12-04

In the date format, there are several main formats for the month:

MM: In digital notation (1-12)

Mon: Abbreviations for the corresponding language month, such as the abbreviation for December in English is Dec.

MON: function as above, except shorthand for uppercase

Month: The full name of the corresponding language, such as December in English is written as December.

MONTH: function as above, except for uppercase

Sql> Select To_char (sysdate, ' Yyyy-mon-dd ') from dual;

To_char (sysdate, ' yyyy-mon-dd ')

----------------------------------------

2015-dec-04

Sql> Select To_char (sysdate, ' Yyyy-month-dd ') from dual;

To_char (sysdate, ' yyyy-month-dd ')

----------------------------------------------------------------------------------------

2015-december-04

Sql> Select To_char (sysdate, ' Yyyy-month-dd ') from dual;

To_char (sysdate, ' yyyy-month-dd ')

----------------------------------------------------------------------------------------

2015-december-04

In the date format, the format of the day is mainly the following:

DD: in digital notation (1-31)

DY: Shorthand for the title of each day of the week in the corresponding language. For example, the abbreviation for Friday in English is Fri.

DY: function as above, change to uppercase

Dy: function as above, change to capital letter

Day: The full name of the title of each week in the corresponding language. For example, the full name of Friday in English is Friday.

Day: function as above, change to uppercase

Day: function as above, change to the first letter capitalization table

Sql> Select To_char (sysdate, ' Yyyy-mm-dy ') from dual;

To_char (sysdate, ' Yyyy-mm-dy ')

----------------------------------------

2015-12-fri

Sql> Select To_char (sysdate, ' Yyyy-mm-dy ') from dual;

To_char (sysdate, ' Yyyy-mm-dy ')

----------------------------------------

2015-12-fri

Sql> Select To_char (sysdate, ' Yyyy-mm-dy ') from dual;

To_char (sysdate, ' Yyyy-mm-dy ')

----------------------------------------

2015-12-fri

Sql> Select To_char (sysdate, ' Yyyy-mm-day ') from dual;

To_char (sysdate, ' yyyy-mm-day ')

----------------------------------------------------------------------------------------

2015-12-friday

Sql> Select To_char (sysdate, "Yyyy-mm-dd Day") from dual;

To_char (sysdate, ' yyyy-mm-ddday ')

----------------------------------------------------------------------------------------------

2015-12-04 Friday

Format suffix

SP: English spelling of the corresponding date part

Th: The number of the corresponding date part day, that is, after the number of the day is added "th"

Sql> Select To_char (sysdate, ' Yyyy-mm-ddth ') from dual;

To_char (sysdate, ' yyyy-mm

------------------------

2015-12-04th

Sql> Select To_char (sysdate, ' yyyy-mm-ddspth ') from dual;

To_char (sysdate, ' yyyy-mm-ddspth ')

--------------------------------------------

2015-12-fourth

Sql> Select To_char (sysdate, ' Yyyy-mmsp-dd ') from dual;

To_char (sysdate, ' yyyy-mmsp-d

----------------------------

2015-twelve-04

Sql> Select To_char (sysdate, ' Yyyy-mmspth-dd ') from dual;

To_char (sysdate, ' yyyy-mmspth-dd ')

--------------------------------

2015-twelfth-04

Sql> Select To_char (sysdate, ' Yyyyth-mm-dd ') from dual;

To_char (sysdate, ' yyyyth-

------------------------

2015th-12-04

Sql> Select To_char (sysdate, ' YYYY-MM-DDTHSP ') from dual;

To_char (sysdate, ' YYYY-MM-DDTHSP ')

--------------------------------------------

2015-12-fourth

Sql> Select To_char (sysdate, ' Yyyysp-mm-dd ') from dual;

To_char (sysdate, ' yyyysp-mm-dd ')

------------------------------------------------------------------------------------------------

Thousand fifteen-12-04

Year: Output the number of years in English spelling

Year: function as above, only uppercase output

Sql> Select To_char (sysdate, ' Year-mm-dd ') from dual;

To_char (sysdate, ' year-mm-dd ')

------------------------------------------------------------------------------------------------

Twenty fifteen-12-04

After the nls_date_language is set to Chinese, the corresponding month and day of the week are expressed in Chinese.

Sql> alter session set nls_date_language= ' Simplified Chinese '; --when there are spaces in the middle of the parameter, enclose the entire parameter in single quotation marks.

Session altered.

Sql> Show Parameter Nls_date

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Nls_date_format string DD-MON-RR

Nls_date_language String Simplified Chinese

Sql> Select To_char (sysdate, ' Yyyy-mm-dd ') from dual;

To_char (sysdate, ' YYY

--------------------

2015-12-04

Sql> Select To_char (sysdate, ' Yyyy-mon-dd ') from dual;

To_char (sysdate, ' yyyy-mon-dd ')

--------------------------------

201 May-December-04

Sql> Select To_char (sysdate, ' Yyyy-month-dd ') from dual;

To_char (sysdate, ' yyyy-month-dd ')

--------------------------------

201 May-December-04

There is no concept of case and abbreviation for date representation in Chinese, dy,dy,dy are the same output, other same.

Sql> Select To_char (sysdate, ' Yyyy-mm-dy ') from dual;

To_char (sysdate, ' Yyyy-mm-dy ')

----------------------------------------

2015-12-Friday

Sql> Select To_char (sysdate, ' Yyyy-mm-dy ') from dual;

To_char (sysdate, ' Yyyy-mm-dy ')

----------------------------------------

2015-12-Friday

Sql> Select To_char (sysdate, ' Yyyy-mm-day ') from dual;

To_char (sysdate, ' yyyy-mm-day ')

----------------------------------------

2015-12-Friday

Sql> Select To_char (sysdate, "Yyyy-mm-dd Day") from dual;

To_char (sysdate, ' yyyy-mm-ddday ')

----------------------------------------------

2015-12-04 Friday

Year,th and SP are limited to the English output representation

Sql> Select To_char (sysdate, ' yyyy-mm-ddspth ') from dual;

To_char (sysdate, ' yyyy-mm-ddspth ')

--------------------------------------------

2015-12-fourth

Sql> Select To_char (sysdate, ' Yyyysp-mm-dd ') from dual;

To_char (sysdate, ' yyyysp-mm-dd ')

------------------------------------------------------------------------------------------------

Thousand fifteen-12-04

Sql> Select To_char (sysdate, ' Year-mm-dd ') from dual;

To_char (sysdate, ' year-mm-dd ')

------------------------------------------------------------------------------------------------

Twenty fifteen-12-04

Sql> Select To_char (sysdate, ' YYYYSP-MMSP-DDSP ') from dual;


To_char (sysdate, ' YYYYSP-MMSP-DDSP ')
--------------------------------------------------------------------------------------------------------------- -------------
Thousand Fifteen-twelve-four

DL: Long date format output

Sql> Select To_char (sysdate, ' DL ') from dual;

To_char (sysdate, ' DL ')

--------------------------------------------------------------

Friday, December 04, 2015

Revised calendar for the ROC calendar, the Annals of the Republic of China to write

Sql> alter session Set Nls_calendar= ' ROC official ';

Session altered.

Sql> Select To_char (sysdate, ' DL ') from dual; --2015-1911=104, that is 2015 A.D. 104

To_char (sysdate, ' DL ')

------------------------------------------------------------

104 December 4 Friday

E: The abbreviation of the corresponding chronological

EE: The full name of the corresponding chronological

Sql> Select To_char (sysdate, ' year-mm-dd e ') from dual;

To_char (sysdate, ' Year-mm-dde ')

--------------------------------------------------------------------------------------------------------------- ---------------

One hundred four-12-04 civil country

Sql> Select To_char (sysdate, ' EE ') from dual;

To_char (sysdate, ' EE ')

------------------------------------------------------------

China Civil

You can specify the NLS parameter in the function, and the entire parameter and value are enclosed in single quotation marks ('), which takes effect only for that execution.

Sql> Select To_char (sysdate, ' yyyy-mm-dd ', ' Nls_calendar=gregorian ') from dual;

To_char (sysdate, ' YYY

--------------------

2015-12-04

Sql> Select To_char (sysdate, ' Yyyy-mon-dd ') from dual;

To_char (sysdate, ' yyyy-mon-dd ')

--------------------------------

0104-12-04

Sql> alter session set Nls_calendar=gregorian;

Session altered.

Sql> Show Parameter Nls_date

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Nls_date_format string DD-MON-RR

Nls_date_language string American

Sql> alter session set nls_date_language= ' Simplified Chinese ';

Session altered.

Sql> Show Parameter Nls_date

NAME TYPE VALUE

------------------------------------ ---------------------- ------------------------------

Nls_date_format string DD-MON-RR

Nls_date_language String Simplified Chinese

Sql> Select To_char (sysdate, ' Yyyy-mon-dd ') from dual;

To_char (sysdate, ' yyyy-mon-dd ')

--------------------------------

201 May-December-04

Sql> Select To_char (sysdate, ' yyyy-mon-dd ', ' nls_calendar= ' ROC official ') from dual; --because there are spaces in the parameter values, the value of the parameter is enclosed in single quotation marks, and two single quotation marks are used to denote a single quotation mark because the outer layer has a single quotation mark.

To_char (sysdate, ' Yyyy-mo

------------------------

0104-12-04

Sql> Select To_char (sysdate, "Yyyy-mon-dd Day") from dual;

To_char (sysdate, ' yyyy-mon-ddday ')

----------------------------------------------------------

201 May-December-04 Friday

Sql> Select To_char (sysdate, ' Yyyy-mon-dd Day ', ' nls_calendar= ' ROC official ") from dual;

To_char (sysdate, ' yyyy-mon-ddday ', ' nls_calendar= ' R

--------------------------------------------------

0104-12-04 Friday

You can specify other characters in the format and enclose the corresponding characters in double quotation marks ("). Output is output as is.

Sql> Select To_char (Sysdate, ' Republic ' Yyyy-mon-dd Day ', ' nls_calendar= ' ROC official ') from dual;

To_char (Sysdate, ' The Republic ' Yyyy-mon-ddday ', ' nls_calendar= ' Rocoffi

--------------------------------------------------------------

Republic of China 0104-12-04 Friday

You can specify more than one NLS parameter in a function, separated by a space between two parameters.

Sql> Select To_char (Sysdate, ' Republic ' Yyyy-mon-dd Day ', ' nls_calendar= ' ROC official ' nls_date_language=american ') from dual;

To_char (Sysdate, ' The Republic ' Yyyy-mon-ddday ', ' nls_calendar= ' rocofficial ' Nls_date_language=american ')

--------------------------------------------------------------------------------------------------------------- -------

Republic of 0104-12-04 Friday

D: the Weekday (1-7) is represented by numbers. The parameter nls_territory the default configuration when the week is starting from Sunday, so Sunday is 1 and Saturday is 7.

Sql> Select To_char (sysdate, ' Yyyy-mm-dd Day d ') from dual;

To_char (sysdate, ' Yyyy-mm-dddayd ')

--------------------------------------------------

2015-12-04 Friday 6

If you want to achieve Monday is 1, Sunday is 7 effect, you can use the following methods to achieve

Sql> Select To_char (sysdate, ' Yyyy-mm-dd Day ', ' nls_date_language= ' numeric date language ") from dual;

To_char (sysdate, ' yyyy-mm-ddday ', '

----------------------------------

2015-12-04 on 5

This article is from the "Big Face Cat" blog, please be sure to keep this source http://bfc99.blog.51cto.com/265386/1719648

Use of date formats in the ORACLE To_char () function