Date and Time parameters (1)

Environment:

[oracle@localhost ~]$ sqlplus -vSQL*Plus: Release 10.2.0.1.0 - Productionsys@ORCL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';Session altered.sys@ORCL> alter session set nls_language='simplified chinese';Session altered.sys@ORCL> select sessiontimezone from dual;SESSIONTIMEZONE---------------------------------------------------------------------------+08:00

(I) Format String:/-: # and so on
Delimiter of the returned string

sys@ORCL> select to_char(sysdate,'yyyy/mm/dd') "/" from dual;/----------2013/01/03     sys@ORCL> select to_char(sysdate,'yyyy-mm-dd') "-" from dual;-----------2013-01-03     sys@ORCL> select to_char(sysdate,'yyyy#mm#dd') "#" from dual;#----------2013#01#03

(Ii) common identifiers
Ad ID: ad or BC

Sys @ orcl> select to_char (sysdate, 'ad yyyy-mm-dd') "ad" from dual; Ad ------------------ ID of the Meridian in 2013-01-03 ad: am pmsys @ orcl> select to_char (sysdate, 'yyyy-mm-dd am hh24: MI: ss') "am" from dual; am--2013-01-03 19:24:36 pm

Century logo: CC SCC
The S prefix specifies that in case of BC display, it will be added before the display (-)
If the last two digits in a year are between 01 and 99 (inclusive), the return value is equal to the first two digits of the year + 1.
If the last two digits of a year are 00, the return value is the same as the first two digits of the year.

sys@ORCL> select to_char(to_date('2013-1-3','YYYY-MM-DD'),'CC') "CC" from dual;CC--21sys@ORCL> select to_char(to_date('2000-1-3','YYYY-MM-DD'),'CC') "CC" from dual;CC--20

(Iii) Common acronyms

D. specify the value of the date in the week (range: 1-7)
Dd specifies the number of days in the current month (range: 1-31)
Ddd specifies the number of days in the current year (range: 1-366)
Day specifies the name of the date in the week

Sys @ orcl> select to_char (to_date ('1970-1-3 ', 'yyyy-mm-dd'), 'D') "DD" from dual; d-5sys @ orcl> select to_char (to_date ('1970-1-3 ', 'yyyy-mm-dd'), 'dd') "DD" from dual; dd -- 03sys @ orcl> select to_char (to_date ('1970-1-3 ', 'yyyy-mm-dd'), 'ddd') "DD" from dual; ddd --- 003 sys @ orcl> select to_char (to_date ('1970-1-3 ', 'yyyy-mm-dd'), 'day') "day" from dual; day --------- Thursday

Mon returns the abbreviated month of the specified date
Month returns the full name of the month of the specified date.

Sys @ orcl> select to_char (sysdate, 'mon') "mon" from dual; Mon -------- January

Year/syear returns the year of the week type. Here, the S prefix is the same as that at SCC.
Yyyy/syyyy returns the number year. Here, the S prefix is the same as that at SCC.
MM returns the month of the specified date (range: 1-12)
HH hour (range: 1-12)
Hh12 hours (range: 1-12)
Hh24 hours (range: 0-23)
Mi returns the minute of the specified time (range: 0-59)
SS returns the number of seconds of the specified time (range: 0-59)

Sys @ orcl> select to_char (sysdate, 'Year') "year" from dual; year -------------------------------------------- twenty thirteensys @ orcl> select to_char (sysdate, 'am hh12 ') "hh12" from dual; hh12 --------- 08 pm

FF [1-9] returns the number of milliseconds. The length can be 1-9. The default value is 6 characters.
Y and YYY return the year separated by commas (,).
Y/yy/YYY returns the year of the date with a specified length

sys@ORCL> select to_char(sysdate,'y') from dual;T-3sys@ORCL> select to_char(sysdate,'yy') from dual;TO--13sys@ORCL> select to_char(sysdate,'yyy') from dual;TO_---013sys@ORCL> select to_char(sysdate,'y,yyy') from dual;TO_CH-----2,013sys@ORCL> select to_char(systimestamp,'ff4') from dual;TO_CHAR(S---------7858

W returns the X week of the specified date in the current month (range: 1-5)
WW returns the X week of the year from the specified date (range: 1-53)
IW specifies the X week of the current year (range: 1-52 or 1-53 (runyear ))

sys@ORCL> select to_char(to_date('2013-1-3','yyyy-mm-dd'),'ww') from dual;TO--01sys@ORCL> select to_char(to_date('2013-1-3','yyyy-mm-dd'),'iw') from dual;TO--01sys@ORCL> select to_char(sysdate,'w') from dual;T-1

Q returns the quarter of the specified date (range: 1-4)
J Total Number of days from January 1, January 1, 4712 BC to the specified date
Sssss returns the number of seconds from midnight to the specified time (range: 0-86399)

sys@ORCL>  select to_char(to_date('2013-1-3','yyyy-mm-dd'),'q') from dual;T-1sys@ORCL> select to_char(sysdate,'J') from dual;TO_CHAR-------2456296