Skip to Main Content
  • Questions
  • SQL to return 12 months of this year

Breadcrumb

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.

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: November 25, 2019 - 12:35 pm UTC

Category: SQL Developer - Version: 17.c

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Travel tip for Kscope15 and Beyond: Bring a water bottle!

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 we 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.


and you rated our response

  (7 ratings)

Reviews

Old school? ;)

October 24, 2019 - 7:51 am UTC

Reviewer: Hugo from Buenos Aires, Argentina

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

Followup  

October 24, 2019 - 8:34 am UTC

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

model

October 24, 2019 - 9:29 am UTC

Reviewer: Anton from Netherlands

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

Followup  

October 24, 2019 - 9:37 am UTC

:)

Date function

October 24, 2019 - 3:23 pm UTC

Reviewer: A reader

Thank you for your reply.
Connor McDonald

Followup  

October 25, 2019 - 5:48 am UTC

glad we could help

October 24, 2019 - 3:30 pm UTC

Reviewer: A reader


But why?

October 28, 2019 - 8:29 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

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

Followup  

October 28, 2019 - 5:27 pm UTC

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

November 04, 2019 - 7:21 am UTC

Reviewer: Abu Mahmoud from GZ PLS

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

Followup  

November 04, 2019 - 1:21 pm UTC

Change all_objects -> dual

And WHERE Rownum -> connect by level

November 22, 2019 - 6:15 pm UTC

Reviewer: A reader

could you please explain PDBs in oracle 12c onwards??
Connor McDonald

Followup  

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 to Explore

SQL

The Oracle documentation contains a complete SQL reference.