Return all months in format of YYYYMM between two given dates with a sql query [message #316876] |
Mon, 28 April 2008 12:51  |
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
|
|
|
|
|
|