Just for fun – weekends til the end of the year
Posted by oakesgr on April 28, 2010
Someone asked me to create a list of dates for all Saturdays and Sundays until the end of the year. Just for fun I asked one of the other dbas to have a go at it too. Here’s the different answers we came up with along with the attached explain plans…
My first attempt after a quick bit of googling
SELECT TO_CHAR (DAY, 'DD-MON-YYYY')
FROM (SELECT SYSDATE - 1 + ROWNUM AS DAY
FROM all_objects
WHERE SYSDATE - 1 + ROWNUM <= TO_DATE ('31/12/2010', 'DD/MM/YYYY'))
WHERE TO_CHAR (DAY, 'D') IN (7, 1)
/
However, I wasn’t particularly happy with this as you have to query all_objects (or some other table) and in the back of my mind I remembered some trick with ‘connect by level’ that I’d seen before to create a list of integers. Google is my friend and I found this on asktom from Mikito Harakiri which I shamelessly copied, hacked and ended up with the following query
SELECT TO_CHAR (SYSDATE + num, 'DD-MON-YYYY')
FROM (SELECT LEVEL num
FROM DUAL
CONNECT BY LEVEL <= 365)
WHERE TO_CHAR (SYSDATE + num, 'D') IN (7, 1)
AND SYSDATE + num <= TO_DATE ('31/12/2010', 'DD/MM/YYYY')
/
In the meantime my dba friend had come up with…
SELECT TRUNC (SYSDATE + a) AS "Saturday", TRUNC (SYSDATE + a) + 1 AS "Sunday"
FROM (SELECT LEVEL a
FROM DUAL
CONNECT BY LEVEL <= 365)
WHERE TO_CHAR (SYSDATE + a, 'D') = 7
AND EXTRACT (YEAR FROM SYSDATE) = EXTRACT (YEAR FROM SYSDATE + a)
/
So it seems that the CONNECT BY LEVEL trick is the way to generate the list of dates, at least that’s the best we found. The filtering functionality can be done in a myriad of ways.
If you’re really interested all the associated explain plans are here. You’ll notice that the plan for the first attempt looks horribly complex compared to the later attempts. However, this is simply a function of using the all_objects view to create the integers to add to sysdate.
Did we miss an easier way? Answers on a postcard to…
Ordinary Oracle Joe 
mwidlake said
How about:
select cal.cal_date
from calendar cal
where to_number(to_char(cal.cal_date,’D’) between 6 and 7
and cal.cal_date between sysdate
and to_date (’31-DEC-2010′,’DD-MON-YYYY’)
/
{nb D starts with 1 for Monday
1* select sysdate, to_char(sysdate,’D’) ,TO_CHAR(SYSDATE,’DAY’) from dual
SYSDATE T TO_CHAR(SYSDATE,’DAY’)
—————– – —————————
28-APR-2010 23:15 3 WEDNESDAY}
That should work.
What was that? You do not have a CALENDAR table in your schema? Why in heck not? we always had one back in the 90’s! It was really useful, like that table you created to hold one row to list the details of you system (name, version, last_patch_applied, system owner, name_of_system,help_system_level etc…)
*sigh*
AM I just getting too old?
OK, I’ll have a think about how to get the weekends when you have no calendar table, and I think you have it spot-on Graham that you need the CONNECT BY LEVEL trick to make it “efficient”. I think something like:
select to_char(sysdate+rownum,’D’)
…
CONNECT BY LEVEL (365-to_char(sysdate,’DDD’))
would be most efficient, as it would only consider the number of days left for the year.
You do realise that if you have to tune this one-off piece of code to get the weekends then you are still running your database on one of Doug’s old Spectrums, don’t you?
oakesgr said
Are you REALLY asking me if you’re getting too old? 🙂 LOL.
Seriously though, how many of those ‘old’ tricks, techniques etc. have we forgotten about these days? shocking! When I was young….
I like your addition so that it only considers the days we need though. I’m sure Doug could run it quicker with his Speccy, but when you’ve only got 48k to play with you need to be tight with your code!
Boneist said
My nls independent version, although sadly without a calendar table:
with mt as (select trunc(sysdate) -1 + level dt
from dual
connect by level <= add_months(trunc(sysdate, 'yyyy'), 12) - trunc(sysdate))
select dt,
to_char(dt, 'Dy', 'nls_date_language=english') dy
from mt
where to_char(dt, 'Dy', 'nls_date_language=english') in ('Sat', 'Sun');
oakesgr said
Boneist, I knew that if anyone would like to perform a little query tuning / rewriting it would be you! like a moth to a flame 🙂
I’m loving the NLS independence, it was bothering me a little bit that I couldn’t run my query the next time I’m in Saudi!
It just goes to show how much we can make of even the simplest requirements when we really want to. What else can we add in? LOL