Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prashant.

Asked: February 10, 2016 - 12:35 pm UTC

Last updated: March 03, 2021 - 8:52 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

How to find all sundays of the year without writing any block or procedure or function?

and Chris said...

You can do this in SQL easily.

Use a row generator to make a list of dates. Then filter these by applying to_char() to them.

The format mask 'fmday' will convert these to days. 

with dts as (
  select date'2016-01-01'+rownum-1 dt from dual
  connect by level <= 366
) 
  select * from dts
  where  to_char(dt, 'fmday') = 'sunday';

DT        
-----------
03-JAN-2016
10-JAN-2016
... <snip> ...
18-DEC-2016
25-DEC-2016

 52 rows selected


Note if you use just 'day' for the format mask, then the conversion pads the day with trailing spaces (up to nine characters).

Also, if you have a table of dates handy you could replace the row generator with a real table.

If you want to know more about row generators, take a look at https://blogs.oracle.com/sql/entry/fizzbuzz

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Also consider NLS (session) settings

Michael, February 15, 2016 - 1:13 pm UTC

Some time ago I used exactly this method for a similiar task - and ran into strange issues because of different NLS settings...

To make this more robust we can specify the NLS language in the TO_CHAR call:

with dts as (
  select date'2016-01-01'+rownum-1 dt from dual
  connect by level <= 366
) 
  select * from dts
  where  to_char(dt, 'fmday', 'NLS_DATE_LANGUAGE=AMERICAN') = 'sunday';


Chris Saxon
February 15, 2016 - 5:56 pm UTC

Good advice. The date masks behave differently for some language settings, so it's good to be explicit.

most useful

A reader, August 23, 2016 - 1:35 pm UTC

great...!!! its works...thanks a lot

Using MOD to limit to counter

Josh Tyler, March 02, 2021 - 7:52 pm UTC

I use this other way to restrict the days without a WHERE condition


select to_char(dt, 'YYYY') || to_char(dt, 'WW') MailYearWk , pickupdt_Sun, scoredt_Wed, dt MailDate_Mon
, to_char(dt, 'DY') as Mail_Day
, to_char(dt, 'WW') as Mail_Week
from ( select distinct
next_day(trunc(sysdate,'YYYY') - 7 , 'SUN') + ((case when mod(level,7) = 0 then level else 0 end)) as pickupdt_Sun ,
next_day(trunc(sysdate,'YYYY') -7 , 'WED') + ((case when mod(level,7) = 0 then level else 0 end)) as scoredt_Wed ,
next_day(trunc(sysdate,'YYYY') , 'MON') + ((case when mod(level,7) = 0 then level else 0 end)) as dt
from dual connect by level <= ( select trunc(sysdate) + 14 - trunc(sysdate , 'YYYY') from dual) order by 1
)
Chris Saxon
March 03, 2021 - 8:52 am UTC

Good point, though I'd use something like:

( level - 1 ) * 7


instead of the case expression
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library