Oracle中 对日期时间类型的操作 — 详细.docx

对日期/时间类型的操作 详细1. oracle对日期的操作 2. 文章分类:数据库 3. 4. 求模：selectmod(7,2)fromdual; 5. 6. 1.oracle日期时间格式： 7. 8. 年: 9. 10. yy两位年显示值：0711. 12. yyyy四位年显示值：200713. 14. 月: 15. 16. mm两位月显示值：1117. 18. month/mon显示值：11月 19. 20. 21. 22. 日： 23. 24. dd当月第几天显示值：0225. 26. ddd当年第几天显示值：0227. 28. dy当周第几天显示值：星期五 29. 30. 31. 32. 小时： 33. 34. hh12小时间显示值：0135. 36. hh2424小时显示值：1337. 38. 39. 40. 分钟： 41. 42. mi显示值：4543. 44. 秒： 45. 46. ss显示值：2547. 48. 49. 50. 季度： 51. 52. Q显示值：453. 54. 55. 56. WW当年第几周显示值：4457. 58. W当月第几周显示值：159. 60. 61. 62. 24小时格式下时间范围为：0:00:00-23:59:5963. 64. 12小时格式下时间范围为：1：00：00-12：59：5965. 66. 67. 68. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual; 69. monday 70. 设置日期语言 71. ALTERSESSIONSETNLS_DATE_LANGUAGE=AMERICAN; 72. 也可以这样 73. TO_DATE(2002-08-26,YYYY-mm-dd,NLS_DATE_LANGUAGE=American) 74. 75. 76. 77. 2.两个日期间的天数 78. 79. selectfloor(sysdate-to_date(2010-3-8,yyyymmdd)fromdual; 80. 81. 82. 83. 注意使用TO_DATE(null),不会报错 84. 85. 86. 87. 3.两个日期间的(除了周六，周日)天数 88. 89. oracle 对日期的操作文章分类:数据库求模：select mod(7,2) from dual;1.oracle日期时间格式：年: yy 两位年 显示值：07 yyyy四位年 显示值：2007月: mm 两位月 显示值：11 month/mon 显示值：11月 日： dd 当月第几天 显示值：02 ddd 当年第几天 显示值：02 dy 当周第几天 显示值：星期五 小时： hh 12小时间 显示值：01 hh24 24小时 显示值：13 分钟： mi 显示值：45秒： ss 显示值：25 季度： Q 显示值：4 WW 当年第几周 显示值：44 W 当月第几周 显示值： 1 24小时格式下时间范围为：0:00:00-23:59:5912小时格式下时间范围为：1：00：00-12：59：59 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) 2.两个日期间的天数 select floor(sysdate-to_date(2010-3-8,yyyymmdd) from dual; 注意使用TO_DATE(null) ,不会报错 3.两个日期间的(除了周六，周日)天数 Java代码 1. Oracle时间日期操作 2. sysdate+(5/24/60/60)在系统时间基础上延迟5秒 3. sysdate+5/24/60在系统时间基础上延迟5分钟 4. sysdate+5/24在系统时间基础上延迟5小时 5. sysdate+5在系统时间基础上延迟5天 6. add_months(sysdate,-5)在系统时间基础上延迟5月 7. add_months(sysdate,-5*12)在系统时间基础上延迟5年 8. 9. 10. 上月末的日期：selectlast_day(add_months(sysdate,-1)fromdual; 11. 本月的最后一秒：selecttrunc(add_months(sysdate,1),MM)-1/24/60/60fromdual 12. 本周星期一的日期：selecttrunc(sysdate,day)+1fromdual 13. 14. 15. 年初至今的天数：selectceil(sysdate-trunc(sysdate,year)fromdual; 16. 17. 今天是今年的第几周:selectto_char(sysdate,fmww)fromdual 18. 今天是本月的第几周:SELECTTO_CHAR(SYSDATE,WW)-TO_CHAR(TRUNC(SYSDATE,MM),WW)+1ASweekOfMonFROMdual 19. 20. 本月的天数 21. SELECTto_char(last_day(SYSDATE),dd)daysFROMdual 22. 今年的天数 23. selectadd_months(trunc(sysdate,year),12)-trunc(sysdate,year)fromdual 24. 下个星期一的日期 25. SELECTNext_day(SYSDATE,monday)FROMdual 26. 27. 28. 29. 30. -计算工作日方法 31. 32. createtablet(sdate,edate); 33. altersessionsetnls_date_format=yyyy-mm-dd; 34. insertintotvalues(2003-03-01,2003-03-03); 35. insertintotvalues(2003-03-02,2003-03-03); 36. insertintotvalues(2003-03-07,2003-03-08); 37. insertintotvalues(2003-03-07,2003-03-09); 38. insertintotvalues(2003-03-05,2003-03-07); 39. insertintotvalues(2003-02-01,2003-03-31); 40. 41. -这里假定日期都是不带时间的，否则在所有日期前加trunc即可。 42. selects,e,e-s+1total_days, 43. trunc(e-s+1)/7)*5+length(replace(substr(01111100111110,to_char(s,d),mod(e-s+1,7),0,)work_days 44. fromt; 45. 46. -droptablet; 47. 48. 引此：/showthread.php?s=1635506cd5f48b1bc3adbe4cde96f227&threadid=104060&perpage=15&pagenumber=1 49. 50. 51. = 52. 53. 判断当前时间是上午下午还是晚上 54. 55. SELECTCASE 56. WHENto_number(to_char(SYSDATE,hh24)BETWEEN6AND11THEN上午57. WHENto_number(to_char(SYSDATE,hh24)BETWEEN11AND17THEN下午58. WHENto_number(to_char(SYSDATE,hh24)BETWEEN17AND21THEN晚上59. END 60. FROMdual; 61. 62. 63. = 64. 65. Oracle中的一些处理日期 66. 67. 68. 69. 将数字转换为任意时间格式.如秒:需要转换为天/小时 70. SELECTto_char(floor(TRUNC(936000/(60*60)/24)|天|to_char(mod(TRUNC(936000/(60*60),24)|小时FROMDUAL 71. 72. 73. TO_DATE格式 74. Day: 75. ddnumber1276. dyabbreviatedfri 77. dayspelledoutfriday 78. ddspthspelledout,ordinaltwelfth 79. Month: 80. mmnumber0381. monabbreviatedmar 82. monthspelledoutmarch 83. Year: 84. yytwodigits9885. yyyyfourdigits199886. 87. 24小时格式下时间范围为：0:00:00-23:59:59. 88. 12小时格式下时间范围为：1:00:00-12:59:59. 89. 1. 90. 日期和字符转换函数用法（to_date,to_char） 91. 92. 2. 93. selectto_char(to_date(222,J),Jsp)fromdual 94. 95. 显示TwoHundredTwenty-Two 96. 97. 3. 98. 求某天是星期几 99. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day)fromdual; 100. 星期一 101. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual; 102. monday 103. 设置日期语言 104. ALTERSESSIONSETNLS_DATE_LANGUAGE=AMERICAN; 105. 也可以这样 106. TO_DATE(2002-08-26,YYYY-mm-dd,NLS_DATE_LANGUAGE=American) 107. 108. 4. 109. 两个日期间的天数 110. selectfloor(sysdate-to_date(20020405,yyyymmdd)fromdual; 111. 112. 5.时间为null的用法 113. selectid,active_datefromtable1 114. UNION 115. select1,TO_DATE(null)fromdual; 116. 117. 注意要用TO_DATE(null) 118. 119. 6. 120. a_datebetweento_date(20011201,yyyymmdd)andto_date(20011231,yyyymmdd) 121. 那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。 122. 所以，当时间需要精确的时候，觉得to_char还是必要的 123. 7.日期格式冲突问题 124. 输入的格式要看你安装的ORACLE字符集的类型,比如:US7ASCII,date格式的类型就是:01-Jan-01125. altersystemsetNLS_DATE_LANGUAGE=American 126. altersessionsetNLS_DATE_LANGUAGE=American 127. 或者在to_date中写 128. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual; 129. 注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， 130. 可查看 131. select*fromnls_session_parameters 132. select*fromV$NLS_PARAMETERS 133. 134. 8. 135. selectcount(*) 136. from(selectrownum-1rnum 137. fromall_objects 138. whererownumselectsysdate,to_char(sysdate,hh)fromdual; 179. 180. SYSDATETO_CHAR(SYSDATE,HH) 181. - 182. 2003-10-1319:35:2107183. 184. SQLselectsysdate,to_char(sysdate,hh24)fromdual; 185. 186. SYSDATETO_CHAR(SYSDATE,HH24) 187. - 188. 2003-10-1319:35:2119189. 190. 获取年月日与此类似 191. 13. 192. 年月日的处理 193. selectolder_date, 194. newer_date, 195. years, 196. months, 197. abs( 198. trunc( 199. newer_date- 200. add_months(older_date,years*12+months) 201. ) 202. )days 203. from(select 204. trunc(months_between(newer_date,older_date)/12)YEARS, 205. mod(trunc(months_between(newer_date,older_date), 206. 12)MONTHS, 207. newer_date, 208. older_date 209. from(selecthiredateolder_date, 210. add_months(hiredate,rownum)+rownumnewer_date 211. fromemp) 212. ) 213. 214. 14. 215. 处理月份天数不定的办法 216. selectto_char(add_months(last_day(sysdate)+1,-2),yyyymmdd),last_day(sysdate)fromdual 217. 218. 16. 219. 找出今年的天数 220. selectadd_months(trunc(sysdate,year),12)-trunc(sysdate,year)fromdual 221. 222. 闰年的处理方法 223. to_char(last_day(to_date(02:year,mmyyyy),dd) 224. 如果是28就不是闰年 225. 226. 17. 227. yyyy与rrrr的区别 228. YYYY99TO_C 229. - 230. yyyy990099231. rrrr991999232. yyyy010001233. rrrr012001234. 235. 18.不同时区的处理 236. selectto_char(NEW_TIME(sysdate,GMT,EST),dd/mm/yyyyhh:mi:ss),sysdate 237. fromdual; 238. 239. 19. 240. 5秒钟一个间隔 241. SelectTO_DATE(FLOOR(TO_CHAR(sysdate,SSSSS)/300)*300,SSSSS),TO_CHAR(sysdate,SSSSS) 242. fromdual 243. 244. 2002-11-19:55:0035786245. SSSSS表示5位秒数 246. 247. 20. 248. 一年的第几天 249. selectTO_CHAR(SYSDATE,DDD),sysdatefromdual 250. 3102002-11-610:03:51251. 252. 21.计算小时,分,秒,毫秒 253. select 254. Days, 255. A, 256. TRUNC(A*24)Hours, 257. TRUNC(A*24*60-60*TRUNC(A*24)Minutes, 258. TRUNC(A*24*60*60-60*TRUNC(A*24*60)Seconds, 259. TRUNC(A*24*60*60*100-100*TRUNC(A*24*60*60)mSeconds 260. from 261. ( 262. select 263. trunc(sysdate)Days, 264. sysdate-trunc(sysdate)A 265. fromdual 266. ) 267. 268. 269. 270. select*fromtabname 271. orderbydecode(mode,FIFO,1,-1)*to_char(rq,yyyymmddhh24miss); 272. 273. / 274. floor(date2-date1)/365)作为年 275. floor(date2-date1,365)/30)作为月 276. mod(mod(date2-date1,365),30)作为日. 277. 23.next_day函数 278. next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。 279. 1234567280. 日一二三四五六 281. 282. - 283. 284. select(sysdate-to_date(2003-12-0312:55:45,yyyy-mm-ddhh24:mi:ss)*24*60*60fromdual 285. 日期返回的是天然后转换为ss 286. 287. 288. 转此：/leadbbs/Announce/Announce.asp?BoardID=42&ID=1769 289. 290. 291. 将数字转换为任意时间格式.如秒:需要转换为天/小时 292. SELECTto_char(floor(TRUNC(936000/(60*60)/24)|天|to_char(mod(TRUNC(936000/(60*60),24)|小时FROMDUAL 293. 294. 295. TO_DATE格式 296. Day: 297. ddnumber12298. dyabbreviatedfri 299. dayspelledoutfriday 300. ddspthspelledout,ordinaltwelfth 301. Month: 302. mmnumber03303. monabbreviatedmar 304. monthspelledoutmarch 305. Year: 306. yytwodigits98307. yyyyfourdigits1998308. 309. 24小时格式下时间范围为：0:00:00-23:59:59. 310. 12小时格式下时间范围为：1:00:00-12:59:59. 311. 1. 312. 日期和字符转换函数用法（to_date,to_char） 313. 314. 2. 315. selectto_char(to_date(222,J),Jsp)fromdual 316. 317. 显示TwoHundredTwenty-Two 318. 319. 3. 320. 求某天是星期几 321. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day)fromdual; 322. 星期一 323. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual; 324. monday 325. 设置日期语言 326. ALTERSESSIONSETNLS_DATE_LANGUAGE=AMERICAN; 327. 也可以这样 328. TO_DATE(2002-08-26,YYYY-mm-dd,NLS_DATE_LANGUAGE=American) 329. 330. 4. 331. 两个日期间的天数 332. selectfloor(sysdate-to_date(20020405,yyyymmdd)fromdual; 333. 334. 5.时间为null的用法 335. selectid,active_datefromtable1 336. UNION 337. select1,TO_DATE(null)fromdual; 338. 339. 注意要用TO_DATE(null) 340. 341. 6. 342. a_datebetweento_date(20011201,yyyymmdd)andto_date(20011231,yyyymmdd) 343. 那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。 344. 所以，当时间需要精确的时候，觉得to_char还是必要的 345. 7.日期格式冲突问题 346. 输入的格式要看你安装的ORACLE字符集的类型,比如:US7ASCII,date格式的类型就是:01-Jan-01347. altersystemsetNLS_DATE_LANGUAGE=American 348. altersessionsetNLS_DATE_LANGUAGE=American 349. 或者在to_date中写 350. selectto_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE=American)fromdual; 351. 注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， 352. 可查看 353. select*fromnls_session_parameters 354. select*fromV$NLS_PARAMETERS 355. 356. 8. 357. selectcount(*) 358. from(selectrownum-1rnum 359. fromall_objects 360. whererownumselectsysdate,to_char(sysdate,hh)fromdual; 401. 402. SYSDATETO_CHAR(SYSDATE,HH) 403. - 404. 2003-10-1319:35:2107405. 406. SQLselectsysdate,to_char(sysdate,hh24)fromdual; 407. 408. SYSDATETO_CHAR(SYSDATE,HH24) 409. - 410. 2003-10-1319:35:2119411. 412. 获取年月日与此类似 413. 13. 414. 年月日的处理 415. selectolder_date, 416. newer_date, 417. years, 418. months, 419. abs( 420. trunc( 421. newer_date- 422. add_months(older_date,years*12+months) 423. ) 424. )days 425. from(select 426. trunc(months_between(newer_date,older_date)/12)YEARS, 427. mod(trunc(months_between(newer_date,older_date), 428. 12)MONTHS, 429. newer_date, 430. older_date 431. from(selecthiredateolder_date, 432. add_months(hiredate,rownum)+rownumnewer_date 433. fromemp) 434. ) 435. 436. 14. 437. 处理月份天数不定的办法 438. selectto_char(add_months(last_day(sysdate)+1,-2),yyyymmdd),last_day(sysdate)fromdual 439. 440. 16. 441. 找出今年的天数 442. selectadd_months(trunc(sysdate,year),12)-trunc(sysdate,year)fromdual 443. 444. 闰年的处理方法 445. to_char(last_day(to_date(02:year,mmyyyy),dd) 446. 如果是28就不是闰年 447. 448. 17. 449. yyyy与rrrr的区别 450. YYYY99TO_C 451. - 452. yyyy990099453. rrrr991999454. yyyy010001455. rrrr012001456. 457. 18.不同时区的处理 458. selectto_char(NEW_TIME(sysdate,GMT,EST),dd/mm/yyyyhh:mi:ss),sysdate 459. fromdual; 460. 461. 19. 462. 5秒钟一个间隔 463. SelectTO_DATE(FLOOR(TO_CHAR(sysdate,SSSSS)/300)*300,SSSSS),TO_CHAR(sysdate,SSSSS) 464. fromdual 465. 466. 2002-11-19:55:0035786467. SSSSS表示5位秒数 468. 469. 20. 470. 一年的第几天 471. selectTO_CHAR(SYSDATE,DDD),sysdatefromdual 472. 3102002-11-610:03:51473. 474. 21.计算小时,分,秒,毫秒 475. select 476. Days, 477. A, 478. TRUNC(A*24)Hours, 479. TRUNC(A*24*60-60*TRUNC(A*24)Minutes, 480. TRUNC(A*24*60*60-60*TRUNC(A*24*60)Seconds, 481. TRUNC(A*24*60*60*100-100*TRUNC(A*24*60*60)mSeconds 482. from 483. ( 484. select 485. trunc(sysdate)Days, 486. sysdate-trunc(sysdate)A 487. fromdual 488. ) 489. 490. 491. 492. select*fromtabname 493. orderbydecode(mode,FIFO,1,-1)*to_char(rq,yyyymmddhh24miss); 494. 495. / 496. floor(date2-date1)/365)作为年 497. floor(date2-date1,365)/30)作为月 498. mod(mod(date2-date1,365),30)作为日. 499. 23.next_day函数 500. next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。 501. 1234567502.