Solved

# how to convert the range of number to the date

Posted on 2013-09-17
Medium Priority
499 Views
i have a query

the 2 parameters are p_numnber_from and p_number_to

and the numbers are 1=JAN
2-FEB
3 - MAR

so if the parameters are from P_number_from =1
p_number_to= 7

how to convert it in the below select statement

SELECT distinct l.ledger_id,
h.period_name,
'2013',
'JAN',
'FEB',
1,
2,
default_effective_date,
m.segment2 legal_entity
FROM apps.gl_code_combinations m,
apps.gl_je_lines l
AND m.code_combination_id = l.code_combination_id
AND l.ledger_id = 2947
AND h.period_name in  ( p_number_from, p_number_to)
0
Question by:pardeshirahul
• 5
• 2
• 2

LVL 78

Expert Comment

ID: 39500598
You can convert the number to the month like the code below but I would suggest a CASE statement.

case p_number
when 1 then 'JAN'
when 1 then 'FEB'
...
end

From a number to a MON:
to_char(to_date(p_number,'MM'),'MON')
0

Author Comment

ID: 39500620
no my question is

if p_number_from =1

and p_number_to = 5

then the query will be

AND h.period_name in  ('JAN-13', 'FEB-13', 'MAR-13', 'APR-13', 'MAY-13')

if p_number_from =9
and p_number_to=10

then

AND h.period_name in  ('SEP-13', 'OCT-13)

thats what i meant

the period_name string
0

LVL 14

Expert Comment

ID: 39500658
You could write a function that converts the 2 input params or do a sub-select with listagg.
Are you familiar with those suggestions?!
0

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 39500659
Off the top of my head, try this:

...
where h.period_name in (
from dual connect by level<=(p_number_to-p_number_from)+1
)
0

LVL 14

Expert Comment

ID: 39500692
@slightwv: nice one *thumbs up*
0

LVL 78

Expert Comment

ID: 39500704
Thanks Alex.

I keep thinking there is an easier way...  I'm just not seeing it right now.
0

Author Comment

ID: 39500719
one more question you mentioned

from dual connect by level<=(p_number_to-p_number_from)+1

but theere is one more parameter to it P_year

so if the year is 2013 then the above query id fine

if it is 2012

then

like can it be dynamic

i have a parameter p_year
0

LVL 78

Expert Comment

ID: 39500737
Again, I think I'm missing the 'easy' way but here is the one with p_year added:

...
...
0

LVL 78

Expert Comment

ID: 39500777
Simplified slightly:
``````...
select to_char(to_date(to_char((level+p_number_from-1),'00') || to_char(p_year),'MMYYYY'),'MON-YY')
from dual connect by level<=(p_number_to-p_number_from)+1
...
``````
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.