[分享]周别相关SQL

Question

在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  
日 一 二 三 四 五 六

Answer 1

在oracle中有很多关于日期的函数，如： 
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()给定一个日期值，返回由第二个参数指出的日子第一次出现在的日期值（应返回相应日子的名称字符串）

Answer 2

在论坛中常常看到有对oracle中时间运算提问的问题，今天有时间，看了看以前各位兄弟的贴子，整理了一下，并作了个示例，希望会对大家有帮助。 
首先感谢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>

Answer 3

日期格式化：
"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.
 

Answer 4

樓主結了吧，我再加入FAQ,應該可以的