Home » SQL & PL/SQL » SQL & PL/SQL » Return all months in format of YYYYMM between two given dates with a sql query
Return all months in format of YYYYMM between two given dates with a sql query [message #316876] Mon, 28 April 2008 12:51 Go to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi All,

I need to get the list of all invoice months in the format of YYYYMM between any two given dates using a sql query.

For example,

select ...... from ....; should result me a output like

200701
200702
200703
-----
-----
200712
200801
----- so on

We can acheive this by plsql block like
#########################################
declare

v_StartDate number(6) := 200701;
v_EndDate number(6) := 201001;
iCount number(3) := v_EndDate - v_StartDate;
k number(6);

begin

for c in 1..iCount
loop

select v_StartDate into k from dual;
dbms_output.put_line(k);

v_StartDate := v_StartDate + 1;
if remainder(v_StartDate,100)=13 then
v_StartDate := v_StartDate + 88;
end if;

end loop;

end;
##########################################

But can we acheive the same functionality using sql statement instead of a plsql block?

Please let me know if it can be acheived.

Thanks in advance.
prashas_d

Re: Return all months in format of YYYYMM between two given dates with a sql query [message #316878 is a reply to message #316876] Mon, 28 April 2008 12:55 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Search for row generator.

You will be using Oracle's connect by command.
Re: Return all months in format of YYYYMM between two given dates with a sql query [message #316881 is a reply to message #316878] Mon, 28 April 2008 13:12 Go to previous messageGo to next message
prashas_d
Messages: 66
Registered: February 2007
Member
Hi Joy_Division,

As you informed, I used the below query

################################################
select INVOICE_MONTH from
(select (200700 + rownum) INVOICE_MONTH from (select 1 column_name from dual connect by level <= 365))
where remainder(INVOICE_MONTH,100) < 13 and remainder(INVOICE_MONTH,100) > 0;
################################################

and its giving me the desired result.

Thanks a lot.
prashas_d.

[Updated on: Mon, 28 April 2008 13:14]

Report message to a moderator

Re: Return all months in format of YYYYMM between two given dates with a sql query [message #316883 is a reply to message #316881] Mon, 28 April 2008 13:19 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Glad to be of service. Next time though, please use code tags to make the code more readable as such:
select INVOICE_MONTH from
(select (200700 + rownum) INVOICE_MONTH 
 from (select 1 column_name from dual connect by level <= 365))
where remainder(INVOICE_MONTH,100) < 13
  and remainder(INVOICE_MONTH,100) > 0;
Previous Topic: Convert Number (16 bytes) to encrypted raw value
Next Topic: Oracle Maestro
Goto Forum:
  


Current Time: Sat Dec 10 01:01:52 CST 2016

Total time taken to generate the page: 0.09234 seconds