Skip to Main Content
  • Questions

  • SQL to return 12 months of this year

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 23, 2019 - 4:00 pm UTC

Last updated: November 25, 2019 - 12:35 pm UTC

Version: 17.c

Viewed 10K+ times! This question is

You Asked

select (to_char(add_months (sysdate,level-10),'Month')) as Month
,to_char(TRUNC(add_months(sysdate,level-10),'month'),'mm/dd/yyyy') as firstdayofthemonth
,to_char(last_day(add_months(sysdate,level-10)),'mm/dd/yyyy') as lastdayofmonth
from dual
connect by level <= 12

I need the current year all 12 months with first day of month and last day of month with out using level and connect by.

please help me.

and Connor said...

Something like this should do the trick

SQL> with t(mth,lst,x) as
  2  (
  3  select trunc(sysdate,'YY') mth, last_day(trunc(sysdate,'YY')) lst, 0 x from dual
  4  union all
  5  select add_months(mth,1), add_months(lst,1), x+1
  6  from  t
  7  where x < 11
  8  )
  9  select mth,lst from t;

MTH       LST
--------- ---------
01-JAN-19 31-JAN-19
01-FEB-19 28-FEB-19
01-MAR-19 31-MAR-19
01-APR-19 30-APR-19
01-MAY-19 31-MAY-19
01-JUN-19 30-JUN-19
01-JUL-19 31-JUL-19
01-AUG-19 31-AUG-19
01-SEP-19 30-SEP-19
01-OCT-19 31-OCT-19
01-NOV-19 30-NOV-19
01-DEC-19 31-DEC-19

12 rows selected.


Rating

  (7 ratings)

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

Comments

Old school? ;)

Hugo, October 24, 2019 - 7:51 am UTC 

select add_months(trunc(sysdate,'yyyy'),rownum-1),add_months(add_months(trunc(sysdate,'yyyy'),rownum-1),1)-1
from dba_objects
where rownum < 13;


ADD_MONTHS(TRUNC(S ADD_MONTHS(ADD_MON
------------------ ------------------
01-ENE-19          31-ENE-19
01-FEB-19          28-FEB-19
01-MAR-19          31-MAR-19
01-ABR-19          30-ABR-19
01-MAY-19          31-MAY-19
01-JUN-19          30-JUN-19
01-JUL-19          31-JUL-19
01-AGO-19          31-AGO-19
01-SEP-19          30-SEP-19
01-OCT-19          31-OCT-19
01-NOV-19          30-NOV-19
01-DIC-19          31-DIC-19

12 filas seleccionadas.

Chris Saxon
October 24, 2019 - 8:34 am UTC

Nice, but not all database users have select privileges on DBA_OBJECTS!

model

Anton, October 24, 2019 - 9:29 am UTC

Or the old school model clause:
select mth,lst
from dual
model
dimension by (1 x)
measures ( cast( null as date ) mth, cast( null as date ) lst)
rules iterate(12) ( mth[iteration_number] = add_months( trunc(sysdate,'Y'), iteration_number )
, lst[iteration_number] = last_day(mth[iteration_number])
)
order by mth

Chris Saxon
October 24, 2019 - 9:37 am UTC

:)

Date function

A reader, October 24, 2019 - 3:23 pm UTC

Thank you for your reply.
Connor McDonald
October 25, 2019 - 5:48 am UTC

glad we could help

A reader, October 24, 2019 - 3:30 pm UTC


But why?

Kim Berg Hansen, October 28, 2019 - 8:29 am UTC

I'd like to question why the original poster has the requirement to do it without connect by and level?

I do not have sufficient fantasy to think of a business reason?

I believe there is no technical reason? I mean, connect by is available in all database versions and editions without extra licensing (well, I don't know about version 7 or older ;-)

But okay, maybe I'm just overthinking it - maybe it's just for the sheer fun of trying to create alternative code using as many techniques as possible. Which can be great fun ;-)
Chris Saxon
October 28, 2019 - 5:27 pm UTC

Good question. A possible reason - to make this "database independent"

Abu Mahmoud, November 04, 2019 - 7:21 am UTC

Hi Conor ,
The following code return all days of prev, current and next year.

SELECT To_Date ( TO_CHAR (To_Date ('01-01', 'DD-MM'), 'DD-MM')
|| TO_CHAR(Sysdate - Interval '1' YEAR, '-YYYY'), 'DD/MM/YYYY' ) + Rownum - 1 Dt
FROM all_objects
WHERE Rownum <= To_Date(TO_CHAR(Sysdate, 'dd/mm/yyyy'), 'dd/mm/yyyy') - To_Date(TO_CHAR(Sysdate - Interval '2' YEAR, 'dd/mm/yyyy'), 'dd/mm/yyyy');

May you help me to replace the following code by another like your way without using all_objects table.

Sincerely Abu Mahmoud,

Chris Saxon
November 04, 2019 - 1:21 pm UTC

Change all_objects -> dual

And WHERE Rownum -> connect by level

A reader, November 22, 2019 - 6:15 pm UTC

could you please explain PDBs in oracle 12c onwards??
Connor McDonald
November 25, 2019 - 12:35 pm UTC

Servers are insanely powerful nowadays. Just a few cores can get ridiculous levels of performance. So you may often have LOTS of databases on one server.

Lets say you have 1 database. You have some files and 60+ background processes to run that database.

Now you have 2 database. Now your server has 120 background processes.

Now you have 20 databases. Your server might easily be powerful to run the *work*, but it is also now having to manage 1200 background processes. You waste resources just managing all that background stuff.

So you could put all those databases into a single database. But what if you have clash of schema names. What is 7 of those 20 databases rely on public synonyms. Now you're stuck...

What we want is 20 "sub-databases" that can all sit within a single "true" database, so that a single set of background processes can look after all of them. Each sub-database would appear like a completely independent database, but they could also be managed collectively as well (eg memory, backup, etc)

That is what pluggable databases are. The ability to have multiple "sub-databases" all being part of a single "true" database infrastructure.

More details here

https://www.oracle.com/database/technologies/multitenant.html

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

SQL

The Oracle documentation contains a complete SQL reference.