​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama

An Interesting Date Question

I came across this interesting question recently on a forum. The questioner wanted to know how to calculate the date of every second and fourth Thursday of each month in a year. My code is a bit clunky but does the job nicely and shows yet another great use of analytics.
select * from
(

select mm,dd,dte,row_number() over (partition by mm order by dte) x    from
(
select to_char(to_date('01-jan-07')+rownum,'mon') mm,
to_char(to_date('01-jan-07')+rownum,'dy') dd ,
to_char(to_date('01-jan-07')+rownum,'dd-mon-yy') cdte ,
to_date(to_char(to_date('01-jan-07')+rownum,'dd-mon-yy')) dte
from all_objects
where to_date('01-jan-07')+rownum <= '31-dec-07'
)
where dd ='thu'
)
where x in ( 2 ,4)
order by 3,4