Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- For any date, return the last business day of that month.
- Monday, May 31 is a holiday,
- [oracle@stormking cdb12102 date]$ cat lbd.sql
- set linesize 200
- set trimspool on
- set pagesize 100
- alter session set nls_date_format = 'yyyy-mm-dd Dy';
- with function lbd ( d in date )
- return date
- is
- ld date;
- lbd date;
- begin
- ld := last_day( d );
- return case to_char(ld,'Mon Dy')
- when 'May Mon' then ld-3
- else
- case to_char(ld,'Dy')
- when 'Sat' then ld-1
- when 'Sun' then ld-2
- else ld
- end
- end;
- end lbd;
- select m.d,
- last_day( m.d )ld,
- lbd( m.d ) lbd
- from
- (
- select add_months(to_date('2015-01-01','yyyy-mm-dd') , level-1)
- + round( 27*dbms_random.value) d
- from dual connect by level <= 12
- union
- select to_date('2021-05-01','yyyy-mm-dd')
- from dual
- )m
- order by d
- /
- quit
- [oracle@stormking cdb12102 date]$ sqlplus u/u@//stormking:1521/pdbm @ lbd.sql
- SQL*Plus: Release 12.1.0.2.0 Production on Fri Oct 23 22:49:10 2015
- Copyright (c) 1982, 2014, Oracle. All rights reserved.
- Last Successful login time: Fri Oct 23 2015 22:34:42 -04:00
- Connected to:
- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
- With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
- and Real Application Testing options
- Session altered.
- D LD LBD
- ----------------------- ----------------------- -----------------------
- 2015-01-23 Fri 2015-01-31 Sat 2015-01-30 Fri
- 2015-02-22 Sun 2015-02-28 Sat 2015-02-27 Fri
- 2015-03-05 Thu 2015-03-31 Tue 2015-03-31 Tue
- 2015-04-21 Tue 2015-04-30 Thu 2015-04-30 Thu
- 2015-05-20 Wed 2015-05-31 Sun 2015-05-29 Fri
- 2015-06-06 Sat 2015-06-30 Tue 2015-06-30 Tue
- 2015-07-09 Thu 2015-07-31 Fri 2015-07-31 Fri
- 2015-08-24 Mon 2015-08-31 Mon 2015-08-31 Mon
- 2015-09-10 Thu 2015-09-30 Wed 2015-09-30 Wed
- 2015-10-28 Wed 2015-10-31 Sat 2015-10-30 Fri
- 2015-11-18 Wed 2015-11-30 Mon 2015-11-30 Mon
- 2015-12-10 Thu 2015-12-31 Thu 2015-12-31 Thu
- 2021-05-01 Sat 2021-05-31 Mon 2021-05-28 Fri
- 13 rows selected.
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement