Finding how many of a particular day there have been this year so far
Barry Moody, June 22, 2002 - 9:56 pm UTC
Super Response!! I took the info you gave me, modified it slightly so just the day and number of days show. It's exactly what I wanted. I enjoy reading the questions and responses. You do a great job.
Keep up the great work.
finding how many of a particular day there have been so far this year
Barry, June 22, 2002 - 10:13 pm UTC
Quick followup. Can this query be done from a table other than ALL_OBJECTS. Some clients may not be permissioned for the ALL_OBJECTS table. Thinking of using DUAL if possible?
Thanks again.
June 23, 2002 - 10:42 am UTC
If the client doesn't have access to ALL_OBJECTS -- they are using an unsupported database. ALL clients have access to ALL_OBJECTS in all cases.
Yes, you could use dual (but access to dual is no more or less assured then access to all_objects). You would do this:
....
from ( select 1 from dual union all
select 2 from dual union all
....
select 7 from dual )
.....
number of weekday between any two dates
A reader, May 13, 2005 - 1:19 pm UTC
Hello Tom,
We are trying to use the below for find out weekdays between any two dates but for some dates it returns correct but for dates its not returning the correct answer.
1* select trunc(((trunc(to_date('20050515','yyyymmdd')-rownum+1))-trunc(to_date('20050501','yyyymmdd'), 'DY' ))/7)+1 counts from all_objects where rownum <= 7
SQL>/
COUNTS
----------
3
2
2
2
2
2
2
7 rows selected.
SQL>ed
Wrote file afiedt.buf
1* select trunc(((trunc(to_date('20050517','yyyymmdd')-rownum+1))-trunc(to_date('20050501','yyyymmdd'), 'DY' ))/7)+1 counts from all_objects where rownum <= 7
SQL>/
COUNTS
----------
3
3
3
2
2
2
2
7 rows selected.
SQL>ed
Wrote file afiedt.buf
1* select trunc(((trunc(to_date('20050517','yyyymmdd')-rownum+1))-trunc(to_date('20050502','yyyymmdd'), 'DY' ))/7)+1 counts from all_objects where rownum <= 7
SQL>/
COUNTS
----------
3
3
3
2
2
2
2
7 rows selected.
SQL>ed
Wrote file afiedt.buf
1* select trunc(((trunc(to_date('20050517','yyyymmdd')-rownum+1))-trunc(to_date('20050505','yyyymmdd'), 'DY' ))/7)+1 counts from all_objects where rownum <= 7
SQL>
SQL>/
COUNTS
----------
3
3
3
2
2
2
2
7 rows selected.
SQL>ed
Wrote file afiedt.buf
1* select trunc(((trunc(to_date('20050517','yyyymmdd')-rownum+1))-trunc(to_date('20050515','yyyymmdd'), 'DY' ))/7)+1 counts from all_objects where rownum <= 7
SQL>
SQL>/
COUNTS
----------
1
1
1
1
1
1
1
Please throw some light on this.
May 13, 2005 - 2:04 pm UTC
read through this -- I didn't really look at your queries, we've had discussions on getting business days between two dates many times.
weekends -- it is all about weekends.
what about weekdays in a particular month
Jack, March 01, 2006 - 4:18 pm UTC
how would this be tweaked to give number of Mondays
in March 2006 ?
March 02, 2006 - 9:03 am UTC
ops$tkyte@ORA9IR2> variable x varchar2(30)
ops$tkyte@ORA9IR2> exec :x := 'MAR-2006'
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select count(*)
2 from (
3 select to_date(:x,'mon-yyyy')+level-1 d
4 from dual
5 connect by level <= last_day(to_date(:x,'mon-yyyy'))- to_date(:x,'mon-yyyy')+1
6 )
7 where to_char(d,'dy') = 'mon'
8 /
COUNT(*)
----------
4
brute force, we could definitely optimize this to do less work...
ok, anticipating "what would that look like"
ops$tkyte@ORA9IR2> select count(*)
2 from (
3 select next_day( to_date(:x,'mon-yyyy')-1, 'MON')+(level-1)*7 d
4 from dual
5 connect by level <= 5
6 )
7 where trunc( d, 'mm' ) = to_date(:x,'mon-yyyy')
8 /
COUNT(*)
----------
4
Knowing there are at most 5 mondays in a month.
problem executing the SQL
Jack, March 03, 2006 - 4:48 pm UTC
tried above from SQL - hardcoding the variables
rec'd
1 select count(*)
2 from (
3 select next_day(to_date('APR-2006','mon-yyyy')-1,'TUE')+level-1 d
4 from dual
5 connect by level <= last_day(to_date('APR-2006','mon-yyyy'))-
6 to_date('APR-2006','mon-yyyy')+1
7 )
8* where to_char(d,'dy') = 'tue'
9 /
from dual
*
ERROR at line 4:
ORA-01436: CONNECT BY loop in user data
what happened ?
March 03, 2006 - 5:59 pm UTC
what version are you using.
ops$tkyte@ORA10GR2> select count(*)
2 from (
3 select next_day(to_date('APR-2006','mon-yyyy')-1,'TUE')+level-1 d
4 from dual
5 connect by level <= last_day(to_date('APR-2006','mon-yyyy'))-
6 to_date('APR-2006','mon-yyyy')+1
7 )
8 where to_char(d,'dy') = 'tue'
9 /
COUNT(*)
----------
5
I'll have to guess that it is not software written "this century" and the dual trick won't work. You'll have to use some table with at least 31 rows.
ops$tkyte@ORA10GR2> select count(*)
2 from (
3 select next_day(to_date('APR-2006','mon-yyyy')-1,'TUE')+rownum-1 d
4 from all_objects
5 where rownum <= last_day(to_date('APR-2006','mon-yyyy'))- to_date('APR-2006','mon-yyyy')+1
6 )
7 where to_char(d,'dy') = 'tue'
8 /
COUNT(*)
----------
5
No of a particular day in month
Anwar, March 04, 2006 - 1:27 am UTC
There's a slightly different way of doing it, where you don't have to use any table other than dual.
SQL> VAR X VARCHAR2(8)
SQL> EXEC :X:='MAR-2006'
PL/SQL procedure successfully completed.
SQL> SELECT decode(
2 to_char(next_day(to_date(:x,'MON-YYYY')-1,'MON')+28,'MON-YYYY'),
3 :x,
4 5,
5 4) no_of_days
6 FROM dual
7 /
NO_OF_DAYS
----------
4
No days in the year so far
Anwar, March 04, 2006 - 1:54 am UTC
SQL> VAR X VARCHAR2(4)
SQL> EXEC :X:='2006'
PL/SQL procedure successfully completed.
SQL> SELECT ceil((to_number(today)-to_number(fst))/7) no_of_days
2 FROM
3 (
4 SELECT to_char(next_day(trunc(to_date(:x,'YYYY'),'YEAR')-1,'WED'),'ddd') fst,
5 to_char(sysdate,'ddd') today
6 FROM dual
7 )
8 /
NO_OF_DAYS
----------
9
A slight modification
Anwar, March 04, 2006 - 2:23 am UTC
Sorry, my last code needs a slight modification to work accurately.
SQL> SELECT ceil((to_number(today+1)-to_number(fst))/7) no_of_days
2 FROM
3 (
4 SELECT to_char(next_day(trunc(to_date(:x,'YYYY'),'YEAR')-1,'TUE'),'ddd') fst,
5 to_char(sysdate,'ddd') today
6 FROM dual
7 )
8 /
NO_OF_DAYS
----------
9
problem executing the SQL
Jack, March 06, 2006 - 7:49 am UTC
using version 8.1.7.4 ( i know ... <sigh> ) --- corporate slow to provide funding for upgrade.
i am trying to write a PL/SQL function that given a month and year ( APR-2006 ) and a weekday ( TUE ) will return the
number of times that weekday occurs in the month/year.
this is needed for some scheduling calculations.
when i take the above code that does not use the dual table
i get the results as indicated. However,April 2006 has only 4 Tuesdays .....
So?
Anwar, March 06, 2006 - 8:42 am UTC
It returns 4
SQL> SELECT decode(
2 to_char(next_day(to_date(:x,'MON-YYYY')-1,'TUE')+28,'MON-YYYY'),
3 :x,
4 5,
5 4) no_of_days
6 FROM dual
7 /
NO_OF_DAYS
----------
4
1 row selected.
It should not be a problem to create function based on it.
Anwar, March 06, 2006 - 8:46 am UTC
SQL> VAR X VARCHAR2(8)
SQL> EXEC :X:='APR-2006'
SQL> SELECT decode(
2 to_char(next_day(to_date(:x,'MON-YYYY')-1,'TUE')+28,'MON-YYYY'),
3 :x,
4 5,
5 4) no_of_days
6 FROM dual
7 /
NO_OF_DAYS
----------
4
1 row selected.
Not Anwar code .... Tom code
Jack, March 06, 2006 - 12:43 pm UTC
I was trying to use Tom's code ....
have not tried Anwar code ....
March 08, 2006 - 4:10 pm UTC
yes, well in 8i the dual trick won't work. and my quick fix for 8i has a "bug" - no +1 should be there.
Finding how many of a particular day there have been this year so far", version 8.1.7
Athma, June 09, 2006 - 2:56 am UTC
Thanks Tom. This is very useful for me, However there is small bug I noticed. In APR-2006, number of TUE is 4, your script is giving 5
after modifying slightly, I got what I wanted and would like to share with our users. I have tested this for FEB-2004 leap year.
T.h.a.n.k.s
Athma
SQL>
SQL> select count(*)
2 from (
3 select to_date('01-APR-2006','dd-mon-yyyy')+rownum-1 d
4 from all_objects
5 where rownum <= last_day(to_date('APR-2006','mon-yyyy'))-
6 to_date('APR-2006','mon-yyyy')+1
7 )
8 where to_char(d,'dy') = 'tue'
9 /
COUNT(*)
----------
4