kooyee ‘s blog

开源软件, 众人努力的结晶, 全人类的共同财富
posts - 103, comments - 55, trackbacks - 0, articles - 66
   :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理

[ORACLE] Oracle update date 和 time

Posted on 2007-07-13 19:46 kooyee 阅读(2982) 评论(0)  编辑  收藏 所属分类: Database数据库技术
调用到 to_date() function

update table 
set column
=to_date('17002000','HH24MIYYYY'
)
    or
    set column=to_date('17:002000','HH24:MIYYYY'
)

select statement (sysdate database的系统中的时间)
select * from table where month=to_date(SYSDATE', 'MM') //to_date() return month

   SELECT TO_CHAR(SYSDATE, 'ddMM') FROM dual; //return day and month in char

to_date( string1, [ format_mask ], [ nls_language ] )


Parameter Explanation
YEAR Year, spelled out
YYYY 4-digit year
YYY
YY
Y
Last 3, 2, or 1 digit(s) of year.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY 4-digit year based on the ISO standard
RRRR Accepts a 2-digit year and returns a 4-digit year.
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
Q Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW 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 Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW Week of year (1-52 or 1-53) based on the ISO standard.
D Day of week (1-7).
DAY Name of day.
DD Day of month (1-31).
DDD Day of year (1-366).
DY Abbreviated name of day.
J Julian day; the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M. Meridian indicator
AD or A.D AD indicator
BC or B.C. BC indicator
TZD Daylight savings information. For example, 'PST'
TZH Time zone hour.
TZM Time zone minute.
TZR Time zone region.


For example:

to_date('2003/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY') would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd')

would return a date value of Mar 15, 2002.


只有注册用户登录后才能发表评论。


网站导航: