在oracle中处理日期大全 TO_DATE格式
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998 24小时格式下时间范围为: 0:00:00 - 23:59:59....
12小时格式下时间范围为: 1:00:00 - 12:59:59 ....
1.
日期和字符转换函数用法(to_date,to_char) 2.
select to_char( to_date(222,'J'),'Jsp') from dual 显示Two Hundred Twenty-Two 3.
求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') 4.
两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; 5. 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual; 注意要用TO_DATE(null) 6.
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以,当时间需要精确的时候,觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS 8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not
in ( '1', '7' ) 查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒). 9.
select months_between(to_date('01-31-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1 select months_between(to_date('02-01-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 1.03225806451613
10. Next_day的用法
Next_day(date, day) Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D 11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意:第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end; select to_char(sys_date,'hh:mi:ss') from all_objects;
12.
获得小时数 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual; SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07 SQL> select sysdate ,to_char(sysdate,'hh24') from dual; SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19 获取年月日与此类似
13.
年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
newer_date,
older_date
from ( select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp )
) 14.
处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual 16.
找出今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年 17.
yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001 18.不同时区的处理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual; 19.
5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual 2002-11-1 9:55:00 35786
SSSSS表示5位秒数 20.
一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51 21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
) select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); //
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
mod(mod(date2-date1, 365), 30)作为日.
23.next_day函数
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998 24小时格式下时间范围为: 0:00:00 - 23:59:59....
12小时格式下时间范围为: 1:00:00 - 12:59:59 ....
1.
日期和字符转换函数用法(to_date,to_char) 2.
select to_char( to_date(222,'J'),'Jsp') from dual 显示Two Hundred Twenty-Two 3.
求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American') 4.
两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual; 5. 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual; 注意要用TO_DATE(null) 6.
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。
所以,当时间需要精确的时候,觉得to_char还是必要的
7. 日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE = American
或者在to_date中写
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多,
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS 8.
select count(*)
from ( select rownum-1 rnum
from all_objects
where rownum <= to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-
02-01','yyyy-mm-dd')+1
)
where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )
not
in ( '1', '7' ) 查找2002-02-28至2002-02-01间除星期一和七的天数
在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1/100秒, 而不是毫秒). 9.
select months_between(to_date('01-31-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL;
1 select months_between(to_date('02-01-1999','MM-DD-YYYY'),
to_date('12-31-1998','MM-DD-YYYY')) "MONTHS" FROM DUAL; 1.03225806451613
10. Next_day的用法
Next_day(date, day) Monday-Sunday, for format code DAY
Mon-Sun, for format code DY
1-7, for format code D 11
select to_char(sysdate,'hh:mi:ss') TIME from all_objects
注意:第一条记录的TIME 与最后一行是一样的
可以建立一个函数来处理这个问题
create or replace function sys_date return date is
begin
return sysdate;
end; select to_char(sys_date,'hh:mi:ss') from all_objects;
12.
获得小时数 SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 2:38:40') from offer
SQL> select sysdate ,to_char(sysdate,'hh') from dual; SYSDATE TO_CHAR(SYSDATE,'HH')
-------------------- ---------------------
2003-10-13 19:35:21 07 SQL> select sysdate ,to_char(sysdate,'hh24') from dual; SYSDATE TO_CHAR(SYSDATE,'HH24')
-------------------- -----------------------
2003-10-13 19:35:21 19 获取年月日与此类似
13.
年月日的处理
select older_date,
newer_date,
years,
months,
abs(
trunc(
newer_date-
add_months( older_date,years*12+months )
)
) days
from ( select
trunc(months_between( newer_date, older_date )/12) YEARS,
mod(trunc(months_between( newer_date, older_date )),
12 ) MONTHS,
newer_date,
older_date
from ( select hiredate older_date,
add_months(hiredate,rownum)+rownum newer_date
from emp )
) 14.
处理月份天数不定的办法
select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual 16.
找出今年的天数
select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 闰年的处理方法
to_char( last_day( to_date('02' || :year,'mmyyyy') ), 'dd' )
如果是28就不是闰年 17.
yyyy与rrrr的区别
'YYYY99 TO_C
------- ----
yyyy 99 0099
rrrr 99 1999
yyyy 01 0001
rrrr 01 2001 18.不同时区的处理
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate
from dual; 19.
5秒钟一个间隔
Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) * 300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')
from dual 2002-11-1 9:55:00 35786
SSSSS表示5位秒数 20.
一年的第几天
select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
310 2002-11-6 10:03:51 21.计算小时,分,秒,毫秒
select
Days,
A,
TRUNC(A*24) Hours,
TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,
TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,
TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds
from
(
select
trunc(sysdate) Days,
sysdate - trunc(sysdate) A
from dual
) select * from tabname
order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss'); //
floor((date2-date1) /365) 作为年
floor((date2-date1, 365) /30) 作为月
mod(mod(date2-date1, 365), 30)作为日.
23.next_day函数
next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。
1 2 3 4 5 6 7
日 一 二 三 四 五 六
解决方案 »
- oracle11g安装后打开时显示找不到文件
- 用PL/SQL语句改掉所有表中包含一个字段的语句 问题
- Oracle存储过程 返回一个集合(列表)?
- 如何可以在oracle 中象sqlserver中一样,可以看到表的结构
- 重命名ORACLE表列名问题。
- oracle的字符集转换问题,我想把US7ASCII字符集的数据库文件导入到ZHS16GBK数据库中,如何修改字符集参数?
- 求助:ORA-23326: 物件群組 "PUBLIC"."REP_MAIN" 不是無聲的--什么意思?
- 两个数据库(Oracle9和SQL Server2000)
- 诚招oracle版分版的版主,有意者请进!
- 请问:如何开始一个事务?
- 数据库字符集的问题。。。。
- 如何截取long字段类型的前n位?
1、add_months()用于从一个日期值增加或减少一些月份
date_value:=add_months(date_value,number_of_months)
例:
SQL> select add_months(sysdate,12) "Next Year" from dual; Next Year
----------
13-11月-04 SQL> select add_months(sysdate,112) "Last Year" from dual; Last Year
----------
13-3月 -13 SQL> 2、current_date()返回当前会放时区中的当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual; SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03 SQL> alter session set time_zone='-11:00'
2 / 会话已更改。 SQL> select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.59.13.668000 下午 -11:
00 SQL> 3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期
timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
+08:00 13-11月-03 11.56.28.160000 上午 +08:
00 SQL> alter session set time_zone='-11:00'
2 / 会话已更改。 SQL> select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.58.00.243000 下午 -11:
00 SQL> 4、dbtimezone()返回时区
varchar_value:=dbtimezone
SQL> select dbtimezone from dual; DBTIME
------
-07:00 SQL> 5、extract()找出日期或间隔值的字段值
date_value:=extract(date_field from [datetime_value|interval_value])
SQL> select extract(month from sysdate) "This Month" from dual; This Month
----------
11 SQL> select extract(year from add_months(sysdate,36)) "3 Years Out" from dual; 3 Years Out
-----------
2006 SQL> 6、last_day()返回包含了日期参数的月份的最后一天的日期
date_value:=last_day(date_value)
SQL> select last_day(date'2000-02-01') "Leap Yr?" from dual; Leap Yr?
----------
29-2月 -00 SQL> select last_day(sysdate) "Last day of this month" from dual; Last day o
----------
30-11月-03 SQL> 7、localtimestamp()返回会话中的日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual; LOCALTIMESTAMP
----------------------------
13-11月-03 12.09.15.433000
下午 SQL> select localtimestamp,current_timestamp from dual; LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ------------------------------------
13-11月-03 12.09.31.006000 13-11月-03 12.09.31.006000 下午 +08:
下午 00 SQL> alter session set time_zone='-11:00'; 会话已更改。 SQL> select localtimestamp,to_char(sysdate,'DD-MM-YYYY HH:MI:SS AM') "SYSDATE" from dual; LOCALTIMESTAMP SYSDATE
---------------------------- ------------------------
12-11月-03 05.11.31.259000 13-11-2003 12:11:31 下午
下午 SQL> 8、months_between()判断两个日期之间的月份数量
number_value:=months_between(date_value,date_value)
SQL> select months_between(sysdate,date'1971-05-18') from dual; MONTHS_BETWEEN(SYSDATE,DATE'1971-05-18')
----------------------------------------
389.855143 SQL> select months_between(sysdate,date'2001-01-01') from dual; MONTHS_BETWEEN(SYSDATE,DATE'2001-01-01')
----------------------------------------
34.4035409 SQL> 9、next_day()给定一个日期值,返回由第二个参数指出的日子第一次出现在的日期值(应返回相应日子的名称字符串)
首先感谢ern、eric.li及各版主还有热心的兄弟们 内容如下:
1、oracle支持对日期进行运算
2、日期运算时是以天为单位进行的
3、当需要以分秒等更小的单位算值时,按时间进制进行转换即可
4、进行时间进制转换时注意加括号(见示例中红色括号),否则会出问题 SQL> alter session set nls_date_format='yyyy-mm-dd hh:mi:ss'; 会话已更改。 SQL> set serverout on
SQL> declare
2 DateValue date;
3 begin
4 select sysdate into DateValue from dual;
5 dbms_output.put_line('源时间:'||to_char(DateValue));
6 dbms_output.put_line('源时间减1天:'||to_char(DateValue-1));
7 dbms_output.put_line('源时间减1天1小时:'||to_char(DateValue-1-1/24));
8 dbms_output.put_line('源时间减1天1小时1分:'||to_char(DateValue-1-1/24-1/(24*60)));
9 dbms_output.put_line('源时间减1天1小时1分1秒:'||to_char(DateValue-1-1/24-1/(24*60)-1/(24*60*6
0)));
10 end;
11 /
源时间:2003-12-29 11:53:41
源时间减1天:2003-12-28 11:53:41
源时间减1天1小时:2003-12-28 10:53:41
源时间减1天1小时1分:2003-12-28 10:52:41
源时间减1天1小时1分1秒:2003-12-28 10:52:40 PL/SQL 过程已成功完成。 SQL>
"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.If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.
For example, 2002 returns 21; 2000 returns 20.
D Yes
Day of week (1-7).
DAY Yes
Name of day, padded with blanks to length of 9 characters.
DD Yes
Day of month (1-31).
DDD Yes
Day of year (1-366).
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 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;
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; JAN = 01).
MON Yes
Abbreviated name of month.
MONTH Yes
Name of month, padded with blanks to length of 9 characters.
PM
P.M. No
Meridian indicator with or without periods.
Q No
Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
RM Yes
Roman numeral month (I-XII; JAN = I).
RR Yes
Lets you store 20th century dates in the 21st century using only two digits. See "The RR Date Format Element" for detailed information.
RRRR Yes
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, then simply enter the 4-digit year.
SS Yes
Second (0-59).
SSSSS Yes
Seconds past midnight (0-86399).
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 "-".
YYYY
SYYYY Yes
4-digit year; "S" prefixes BC dates with "-".
YYY
YY
Y Yes
Last 3, 2, or 1 digit(s) of year.