Advertisement
ExaGridDba

last business day function

Oct 23rd, 2015
195
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.41 KB | None | 0 0
  1. For any date, return the last business day of that month.
  2. Monday, May 31 is a holiday,
  3.  
  4. [oracle@stormking cdb12102 date]$ cat lbd.sql
  5. set linesize 200
  6. set trimspool on
  7. set pagesize 100
  8. alter session set nls_date_format = 'yyyy-mm-dd Dy';
  9.  
  10. with function lbd ( d in date )
  11. return date
  12. is
  13. ld date;
  14. lbd date;
  15. begin
  16. ld := last_day( d );
  17. return case to_char(ld,'Mon Dy')
  18. when 'May Mon' then ld-3
  19. else
  20. case to_char(ld,'Dy')
  21. when 'Sat' then ld-1
  22. when 'Sun' then ld-2
  23. else ld
  24. end
  25. end;
  26. end lbd;
  27. select m.d,
  28. last_day( m.d )ld,
  29. lbd( m.d ) lbd
  30. from
  31. (
  32. select add_months(to_date('2015-01-01','yyyy-mm-dd') , level-1)
  33. + round( 27*dbms_random.value) d
  34. from dual connect by level <= 12
  35. union
  36. select to_date('2021-05-01','yyyy-mm-dd')
  37. from dual
  38. )m
  39. order by d
  40. /
  41.  
  42. quit
  43. [oracle@stormking cdb12102 date]$ sqlplus u/u@//stormking:1521/pdbm @ lbd.sql
  44.  
  45. SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 22:49:10 2015
  46.  
  47. Copyright (c) 1982, 2014, Oracle. All rights reserved.
  48.  
  49. Last Successful login time: Fri Oct 23 2015 22:34:42 -04:00
  50.  
  51. Connected to:
  52. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  53. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  54. and Real Application Testing options
  55.  
  56.  
  57. Session altered.
  58.  
  59.  
  60. D LD LBD
  61. ----------------------- ----------------------- -----------------------
  62. 2015-01-23 Fri 2015-01-31 Sat 2015-01-30 Fri
  63. 2015-02-22 Sun 2015-02-28 Sat 2015-02-27 Fri
  64. 2015-03-05 Thu 2015-03-31 Tue 2015-03-31 Tue
  65. 2015-04-21 Tue 2015-04-30 Thu 2015-04-30 Thu
  66. 2015-05-20 Wed 2015-05-31 Sun 2015-05-29 Fri
  67. 2015-06-06 Sat 2015-06-30 Tue 2015-06-30 Tue
  68. 2015-07-09 Thu 2015-07-31 Fri 2015-07-31 Fri
  69. 2015-08-24 Mon 2015-08-31 Mon 2015-08-31 Mon
  70. 2015-09-10 Thu 2015-09-30 Wed 2015-09-30 Wed
  71. 2015-10-28 Wed 2015-10-31 Sat 2015-10-30 Fri
  72. 2015-11-18 Wed 2015-11-30 Mon 2015-11-30 Mon
  73. 2015-12-10 Thu 2015-12-31 Thu 2015-12-31 Thu
  74. 2021-05-01 Sat 2021-05-31 Mon 2021-05-28 Fri
  75.  
  76. 13 rows selected.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement