當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle常用的时间格式转换

發(fā)布時間：2025/3/20 编程问答 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle常用的时间格式转换小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

1:取得當前日期是本月的第幾周?
?select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual;?

TO_CHAR(SYSDATE,'YY')?

select to_char(sysdate,'W') from dual;?

2:取得當前日期是一個星期中的第幾天,注意星期日是第一天?

select sysdate,to_char(sysdate,'D') from dual;?

　　類似:

select to_char(sysdate,'yyyy') from dual; --年?
select to_char(sysdate,'Q' from dual; --季?
select to_char(sysdate,'mm') from dual; --月?
select to_char(sysdate,'dd') from dual; --日?
ddd 年中的第幾天?
WW 年中的第幾個星期?
W 該月中第幾個星期?
D 周中的星期幾?
hh 小時(12)?
hh24 小時(24)?
Mi 分?
ss 秒?

3:取當前日期是星期幾中文顯示:?
select to_char(sysdate,'day') from dual;?
TO_CHAR(SYSDATE,'DAY')?

星期四?

4:如果一個表在一個date類型的字段上面建立了索引，如何使用?
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'?

5: 得到當前的日期?
select sysdate from dual;

6: 得到當天凌晨0點0分0秒的日期?
select trunc(sysdate) from dual;?
-- 得到這天的最后一秒?
select trunc(sysdate) + 0.99999 from dual;?
-- 得到小時的具體數(shù)值?
select trunc(sysdate) + 1/24 from dual;?
select trunc(sysdate) + 7/24 from dual;?

7.得到明天凌晨0點0分0秒的日期?
select trunc(sysdate+1) from dual;?
select trunc(sysdate)+1 from dual;

8: 本月一日的日期?
select trunc(sysdate,'mm') from dual;?

9:得到下月一日的日期?
select trunc(add_months(sysdate,1),'mm') from dual;?

10:返回當前月的最后一天??
select last_day(sysdate) from dual;?
select last_day(trunc(sysdate)) from dual;?
select trunc(last_day(sysdate)) from dual;?
select trunc(add_months(sysdate,1),'mm') - 1 from dual;?

11: 得到一年的每一天?
select trunc(sysdate,'yyyy')+ rn -1 date0?
from?
(select rownum rn from all_objects?
where rownum<366);

12:今天是今年的第N天?
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;?

13:如何在給現(xiàn)有的日期加上2年?
select add_months(sysdate,24) from dual;?

14:判斷某一日子所在年分是否為潤年?
select decode(to_char(last_day(trunc(sysdate,'y')+31),'dd'),'29','閏年','平年') from dual;?

15:判斷兩年后是否為潤年?
select decode(to_char(last_day(trunc(add_months(sysdate,24),'y')+31),?
'dd'),'29','閏年','平年') from dual;

16:得到日期的季度?
select ceil(to_number(to_char(sysdate,'mm'))/3) from dual;?
select to_char(sysdate, 'Q') from dual;?

TO_DATE格式(以時間:2007-11-02?? 13:45:25為例)
???
??????? Year:??????
??????? yy two digits 兩位年??????????????? 顯示值:07
??????? yyy three digits 三位年??????????????? 顯示值:007
??????? yyyy four digits 四位年??????????????? 顯示值:2007
????????????
??????? Month:??????
??????? mm??? number???? 兩位月????????????? 顯示值:11
??????? mon??? abbreviated 字符集表示????????? 顯示值:11月,若是英文版,顯示nov?????
??????? month spelled out 字符集表示????????? 顯示值:11月,若是英文版,顯示november?
??????????
??????? Day:??????
??????? dd??? number???????? 當月第幾天??????? 顯示值:02
??????? ddd??? number???????? 當年第幾天??????? 顯示值:02
??????? dy??? abbreviated 當周第幾天簡寫??? 顯示值:星期五,若是英文版,顯示fri
??????? day??? spelled out?? 當周第幾天全寫??? 顯示值:星期五,若是英文版,顯示friday????????
??????? ddspth spelled out, ordinal twelfth?
?????????????
????????????? Hour:
????????????? hh??? two digits 12小時進制??????????? 顯示值:01
????????????? hh24 two digits 24小時進制??????????? 顯示值:13
??????????????
????????????? Minute:
????????????? mi??? two digits 60進制??????????????? 顯示值:45
??????????????
????????????? Second:
????????????? ss??? two digits 60進制??????????????? 顯示值:25
??????????????
????????????? 其它
????????????? Q???? digit???????? 季度????????????????? 顯示值:4
????????????? WW??? digit???????? 當年第幾周??????????? 顯示值:44
????????????? W??? digit????????? 當月第幾周??????????? 顯示值:1
??????????????
??????? 24小時格式下時間范圍為： 0:00:00 - 23:59:59....??????
??????? 12小時格式下時間范圍為： 1:00:00 - 12:59:59 ....?
????????????
1. 日期和字符轉(zhuǎn)換函數(shù)用法（to_date,to_char）
?????????
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;?? //日期轉(zhuǎn)化為字符串???
select to_char(sysdate,'yyyy') as nowYear?? from dual;?? //獲取時間的年???
select to_char(sysdate,'mm')??? as nowMonth from dual;?? //獲取時間的月???
select to_char(sysdate,'dd')??? as nowDay??? from dual;?? //獲取時間的日???
select to_char(sysdate,'hh24') as nowHour?? from dual;?? //獲取時間的時???
select to_char(sysdate,'mi')??? as nowMinute from dual;?? //獲取時間的分???
select to_char(sysdate,'ss')??? as nowSecond from dual;?? //獲取時間的秒

????
select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')??? from dual//

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. 兩個日期間的天數(shù)??????
??? 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點之前是不包含在這個范圍之內(nèi)的。??????
?? 所以，當時間需要精確的時候，覺得to_char還是必要的?
??????
7. 日期格式?jīng)_突問題??????
??? 輸入的格式要看你安裝的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間除星期一和七的天數(shù)??????
?? 在前后分別調(diào)用DBMS_UTILITY.GET_TIME, 讓后將結(jié)果相減(得到的是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 與最后一行是一樣的??????
?? 可以建立一個函數(shù)來處理這個問題??????
?? 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.獲得小時數(shù)??????
???? extract()找出日期或間隔值的字段值
??? 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')??????
? ???
????
??? SQL> select sysdate ,to_char(sysdate,'hh24') from dual;??????
????
??? SYSDATE TO_CHAR(SYSDATE,'HH24')??????
??

???????
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.處理月份天數(shù)不定的辦法??????
?? select to_char(add_months(last_day(sysdate) +1, -2), 'yyyymmdd'),last_day(sysdate) from dual????

16.找出今年的天數(shù)??????
?? 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的區(qū)別??????
?? 'YYYY99 TO_C??????
?? ------- ----??????
?? yyyy 99 0099??????
?? rrrr 99 1999??????
?? yyyy 01 0001??????
?? rrrr 01 2001????

18.不同時區(qū)的處理??????
?? 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位秒數(shù)????

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) 作為月??????
?? d(mod(date2-date1, 365), 30)作為日.

23.next_day函數(shù)????? 返回下個星期的日期,day為1-7或星期日-星期六,1表示星期日
?? next_day(sysdate,6)是從當前開始下一個星期五。后面的數(shù)字是從星期日開始算起。??????
?? 1 2 3 4 5 6 7??????
?? 日一二三四五六????
???
?? ---------------------------------------------------------------?
???
?? select??? (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-dd hh24:mi:ss'))*24*60*60 from ddual
?? 日期返回的是天然后轉(zhuǎn)換為ss
?????
24,round[舍入到最接近的日期](day:舍入到最接近的星期日)
?? select sysdate S1,
?? round(sysdate) S2 ,
?? round(sysdate,'year') YEAR,
?? round(sysdate,'month') MONTH ,
?? round(sysdate,'day') DAY from dual

25,trunc[截斷到最接近的日期,單位為天] ,返回的是日期類型
?? select sysdate S1,?????????????????????
???? trunc(sysdate) S2,???????????????? //返回當前日期,無時分秒
???? trunc(sysdate,'year') YEAR,??????? //返回當前年的1月1日,無時分秒
???? trunc(sysdate,'month') MONTH ,???? //返回當前月的1日,無時分秒
???? trunc(sysdate,'day') DAY?????????? //返回當前星期的星期天,無時分秒
?? from dual

26,返回日期列表中最晚日期
?? select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

27.計算時間差
???? 注:oracle時間差是以天數(shù)為單位,所以換算成年月,日
?????
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual??????? //時間差-年
????? select ceil(moths_between(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual??????? //時間差-月
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual???????????? //時間差-天
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual???????? //時間差-時
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual??? //時間差-分
????? select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //時間差-秒

28.更新時間
???? 注:oracle時間加減是以天數(shù)為單位,設改變量為n,所以換算成年月,日
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual??????? //改變時間-年
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual???????????????????????????????? //改變時間-月
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual??????????? //改變時間-日
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual???????? //改變時間-時
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual????? //改變時間-分
???? select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual?? //改變時間-秒

29.查找月的第一天,最后一天
???? SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
?????? Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
?????? Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
?????? LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
?? FROM dual;

轉(zhuǎn)自：http://262002954-qq-com.iteye.com/blog/1119702

1:取得當前日期是本月的第幾周?
SQL> select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual;?
TO_CHAR(SYSDATE,'YY?
-------------------?
20030327 4 18:16:09?
SQL> select to_char(sysdate,'W') from dual;?
T?
-?
4?

2:取得當前日期是一個星期中的第幾天,注意星期日是第一天?
SQL> select sysdate,to_char(sysdate,'D') from dual;?
SYSDATE T?
--------- -?
27-MAR-03 5??
　　類似:

3:取當前日期是星期幾中文顯示:?
SQL> select to_char(sysdate,'day') from dual;?
TO_CHAR(SYSDATE,'DAY')?
----------------------?
星期四?