Home » SQL & PL/SQL » SQL & PL/SQL » find friday the 13th's!
find friday the 13th's! [message #209345] Thu, 14 December 2006 07:11 Go to next message
toenie2
Messages: 7
Registered: November 2006
Junior Member
Hi,

I'm trying to find out how many friday the 13th's there are between today and, let's say, 2010. Can you help?

Thx
Re: find friday the 13th's! [message #209348 is a reply to message #209345] Thu, 14 December 2006 07:19 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I wrote it so you can fairly easily see all the dates, if you want:

select count(*) from
(select sysdate+rownum as fri13date, 
        (case when to_char(sysdate+rownum, 'DD') = 13 AND to_char(sysdate+rownum, 'DY') = 'FRI' then 1 end) as fri13ind
from dual
connect by level <= (to_date('01-jan-2010', 'DD-MON-YYYY') - trunc(sysdate)))
where fri13ind=1
Re: find friday the 13th's! [message #209350 is a reply to message #209345] Thu, 14 December 2006 07:23 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
SQL> SELECT trunc(add_months(sysdate,l),'MM')+12 x
  2  FROM  ( select level l
  3          from dual
  4          connect by level < 48
  5         )
  6  WHERE  to_char( trunc(add_months(sysdate,l),'MM')+12
  7                ,'DY','NLS_DATE_LANGUAGE=ENGLISH') = 'FRI'
  8  /

X
---------
13-APR-07
13-JUL-07
13-JUN-08
13-FEB-09
13-MAR-09
13-NOV-09
13-AUG-10

7 rows selected.


MHE
Re: find friday the 13th's! [message #209353 is a reply to message #209350] Thu, 14 December 2006 07:37 Go to previous messageGo to next message
toenie2
Messages: 7
Registered: November 2006
Junior Member
Nicely done! I still don't understand the code, but I'll figure that out.

Thx
Re: find friday the 13th's! [message #209354 is a reply to message #209353] Thu, 14 December 2006 07:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
toenie2 wrote on Thu, 14 December 2006 14:37
Nicely done! I still don't understand the code, but I'll figure that out.

Thx

Check out row generator techniques.

MHE
Re: find friday the 13th's! [message #210726 is a reply to message #209345] Fri, 22 December 2006 01:26 Go to previous messageGo to next message
sandeepshahare
Messages: 20
Registered: December 2006
Location: pune
Junior Member
PLEASE CHECK THIS........


select *
from (select sysdate+level,to_char(sysdate+level,'DD'),to_char(sysdate+level,'DAY')
from dual
WHERE to_char(sysdate+level,'DD')='13'AND TO_CHAR(SYSDATE+LEVEL,'D')='6'
connect by LEVEL<=SYSDATE- add_months(sysdate,MONTHS_BETWEEN(SYSDATE,'22-DEC-2010')))
/

SYSDATE+L TO TO_CHAR(SYSDATE+LEVEL,'DAY'
--------- -- ---------------------------
13-APR-07 13 FRIDAY
13-JUL-07 13 FRIDAY
13-JUN-08 13 FRIDAY
13-FEB-09 13 FRIDAY
13-MAR-09 13 FRIDAY
13-NOV-09 13 FRIDAY
13-AUG-10 13 FRIDAY

7 rows selected.

[Updated on: Fri, 22 December 2006 01:28]

Report message to a moderator

Re: find friday the 13th's! [message #210827 is a reply to message #210726] Fri, 22 December 2006 08:52 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
sandeepshahare wrote on Fri, 22 December 2006 02:26
add_months(sysdate,MONTHS_BETWEEN(SYSDATE,'22-DEC-2010')))


MONTHS_BETWEEN takes on two DATE parameters, not a DATE and VARCHAR2.

foobar SCOTT>alter session set nls_date_format='mm/dd/yyyy';

Session altered.

foobar SCOTT>select *
  2  from (select sysdate+level,to_char(sysdate+level,'DD'),to_char(sysdate+level,'DAY')
  3  from dual
  4  WHERE to_char(sysdate+level,'DD')='13'AND TO_CHAR(SYSDATE+LEVEL,'D')='6'
  5  connect by LEVEL<=SYSDATE- add_months(sysdate,MONTHS_BETWEEN(SYSDATE,'22-DEC-2010')))
  6  /
connect by LEVEL<=SYSDATE- add_months(sysdate,MONTHS_BETWEEN(SYSDATE,'22-DEC-2010')))
                                                                     *
ERROR at line 5:
ORA-01843: not a valid month



In an expert forum, I would expect to see proper code.

[Updated on: Fri, 22 December 2006 08:59]

Report message to a moderator

Re: find friday the 13th's! [message #212893 is a reply to message #210827] Mon, 08 January 2007 15:01 Go to previous message
herodt
Messages: 2
Registered: January 2007
Junior Member
You are correct Sandeepshahare. I think we should all demand our money back.

Smile
Previous Topic: diff b/n Join& merge
Next Topic: Asynchronous execution with DBMS_SCHEDULER
Goto Forum:
  


Current Time: Sun Dec 11 04:10:07 CST 2016

Total time taken to generate the page: 0.09426 seconds